Apex Code Development You need to sign in to do that
Don't have an account?
nksf Date/Time Formula Field for Workflow Rule
Hi Guys,
I need your help to right a formula which I want to use in my workflow rule. Formula should be if Now() the time is < 4 p.m. EST then set the date as Today() and if Now() the time is > 4 p.m. EST then set the date as tomorrow's Date.
Thanks!!!
I need your help to right a formula which I want to use in my workflow rule. Formula should be if Now() the time is < 4 p.m. EST then set the date as Today() and if Now() the time is > 4 p.m. EST then set the date as tomorrow's Date.
Thanks!!!
But there's one more thing I missed in above formula, i.e you need to compare the time with EST, which is 4 hours behind GMT.
Here's the correct formula to compare time in EST
IF(VALUE(MID ( TEXT ( NOW()- 0.1666 ), 12, 2))<16, TODAY() ,TODAY()+1)
Hope this helps !
Thanks,
Apoorv
All Answers
Please try this :
If you want to set date, then set Formula Return Type to Date and use the below formula
IF(VALUE(MID ( TEXT ( NOW() ), 12, 2))>16, TODAY() ,TODAY()+1)
Please mark this as solved if this helps you !
Thanks,
Apoorv
Thanks for your quick reply. I just want to make sure it should be >16 or <16? If now the time is less than 4 p.m. then it should show today's date otherwise tomorrow's date.
But there's one more thing I missed in above formula, i.e you need to compare the time with EST, which is 4 hours behind GMT.
Here's the correct formula to compare time in EST
IF(VALUE(MID ( TEXT ( NOW()- 0.1666 ), 12, 2))<16, TODAY() ,TODAY()+1)
Hope this helps !
Thanks,
Apoorv
Glad to be of help !
Please mark this as the Best Answer for this post, so that other users can also be benefiited and view this as a proper solution.
Thanks,
Apoorv
I tried to use this formula in Workflow rule to update Date/Time field but it is not working with -0.1666. (Nothing being populated)
Here is my formula
IF(VALUE(MID(TEXT(NOW()- 0.1666 ),12,2))<16 && ISBLANK(Hold_Email_Release_Date__c),NOW()+ 0.1666,
IF(VALUE(MID(TEXT(NOW()- 0.1666 ),12,2))> 16 && ISBLANK(Hold_Email_Release_Date__c),NOW()+1.1666,
DATETIMEVALUE( Hold_Email_Release_Date__c)+(13/24)))
It did work though without -0.1666
IF(VALUE(MID(TEXT(NOW()),12,2))<16 && ISBLANK(Hold_Email_Release_Date__c),NOW(),
IF(VALUE(MID(TEXT(NOW()),12,2))> 16 && ISBLANK(Hold_Email_Release_Date__c),NOW()+1,
DATETIMEVALUE( Hold_Email_Release_Date__c)+(13/24)))
The reason I am looking for 4 p.m. time because I have scheduled a report to run at 4 p.m EST and report is showing all the records which have this date field as Today(). So if Status completed after 4 p.m. EST than this date field should have the date as tomorrow's day. This way tomorrow all these records will appear on the scheduled report.
That also depends on what Time Zone for the user. If its EST then following formula should work :
IF(VALUE(MID(TEXT(NOW()- 0.1666 ),12,2))<16 && ISBLANK(Hold_Email_Release_Date__c),NOW(),
IF(VALUE(MID(TEXT(NOW()- 0.1666 ),12,2))> 16 && ISBLANK(Hold_Email_Release_Date__c),NOW()+1,
DATETIMEVALUE( Hold_Email_Release_Date__c)+(13/24)))
Hope this helps!