 ShowAll Questionssorted byDate Posted Matan Alon

Hi,
I'm trying to write a lead assignment rule base on lead creation time.
I wrote the following formula:

if(and (VALUE( MID( TEXT( CreatedDate ), 12, 2 ) )>=4 ,
VALUE( MID( TEXT( CreatedDate ), 12, 2 ) )<14),true,false)

sadly there something wrong with it and i dont know what...
Is there something i'm missing ? Chinmay Bhusari
Hi Matan,
Can you please specify what you are trying to do, so i can help you, as the code seems to show no errors Matan Alon
I'm trying to assign lead that created between hours 4 and 14 to a specific user.
That formula i wrote in the "Assignment Rules" section. Chinmay Bhusari Chinmay Bhusari
Finding the Hour, Minute, or Second from a Date/Time
To get the hour, minute, and second from a Date/Time field as a numerical value, use the following formulas where TZoffset is the difference between the user’s time zone and GMT. For hour in 24–hour format:
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )
For hour in 12–hour format:
IF(
OR(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
),
12,
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
0,
12
)
)
For minutes:
VALUE( MID( TEXT( date/time - TZoffset ), 15, 2 ) )
For seconds:
VALUE( MID( TEXT( date/time - TZoffset ), 18, 2 ) )
And, to get “AM”. or “PM” as a string, use:
IF(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
"AM",
"PM"
)
To return the time as a string in “HH:MM:SS A/PM” format, use the following formula:
IF(
OR(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 0,
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) )
-
IF(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
0,
12
)
)
)
& ":" &
MID( TEXT( date/time - TZoffset ), 15, 2 )
& ":" &
MID( TEXT( date/time - TZoffset ), 18, 2 )
& " " &
IF(
VALUE( MID( TEXT( date/time - TZoffset ), 12, 2 ) ) < 12,
"AM",
"PM"
)
When working with time in formula fields, you need to consider the time difference between your organization and GMT. See A Note About Date/Time and Time Zones for help understanding the time zone offset used in this formula. Matan Alon
Thanks for your help and effort.
As far as i understand my formula fit what you have wrote. if i want to assign all the leads that created between 4 and 14 in GMT time my formula needs to do the job, not ? Chinmay Bhusari
Yes that should do the job. If still it is not working let me know. bhanu_prakash
IF( OR( VALUE( MID( TEXT(CreatedDate + 10), 12, 2 ) ) = 0, VALUE( MID( TEXT( CreatedDate + 10), 12, 2 ) ) = 12 ), "12", TEXT( VALUE( MID( TEXT( CreatedDate + 10), 12, 2 ) ) - IF( VALUE( MID( TEXT( CreatedDate + 10), 12, 2 ) ) < 12, 0, 12 ) ) ) & ":" & MID( TEXT( CreatedDate + 10), 15, 2 ) & " " & IF( VALUE( MID( TEXT( CreatedDate + 10), 12, 2 ) ) < 12, "PM", "AM" )