+ Start a Discussion
Matan AlonMatan Alon 

Lead Assignment Rule

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 ?

Thanks in advance.
Chinmay BhusariChinmay 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 AlonMatan 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 BhusariChinmay 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 AlonMatan 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 BhusariChinmay Bhusari
Yes that should do the job. If still it is not working let me know.
bhanu_prakashbhanu_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" )