function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
nksfnksf 

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!!!
Best Answer chosen by nksf
Apoorv Saxena 4Apoorv Saxena 4
Yes, you're right.

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

Apoorv Saxena 4Apoorv Saxena 4
Hi,

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
nksfnksf
Hi 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. 
Apoorv Saxena 4Apoorv Saxena 4
Yes, you're right.

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
This was selected as the best answer
nksfnksf
Thanks Apporv!!! I was thinking about EST. It worked with <16. Great thanks!!!
Apoorv Saxena 4Apoorv Saxena 4
Hi,

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
nksfnksf
Hi once again,
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.
Apoorv Saxena 4Apoorv Saxena 4
Hi,

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!
nksfnksf
Thanks a lot Apoorv!!!!