function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Stephen02Stephen02 

Date Formula - calculate the date to show tomorrow's date, IF after a specific hour in the current day

Hi - I am attempting to write a case field formula that will calculate the following:
I would like it to calculate the following requirements:
* IF it is after 1 PM MT, then display tomorrow's date. 

Use case example:
It is 10 AM MT on May 2 2016 then it should show/calculate to May 2 2016
It is 2 PM MT on May 2 2016 then it should show/calculate to May 3 2016

Thus far I have the following, but running it tonight May 2 2016 at 8:45 PM MT, it still calculates/displays May 2 2016:
IF( VALUE(LEFT(RIGHT(TEXT(NOW()),9),2)) < 19, TODAY(), TODAY()+1 )

Thanks for the help.
Best Answer chosen by Stephen02
Bryan JamesBryan James
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 13:00:00" ),TODAY(),TODAY() + 1)

Something like that might work. I am converiting todays year, month, and day by using the TEXT() formula type to concatinate them with a time of 1300 hours so that the DATETIMEVALUE converts the string "YYYY-MM-DD HH:MM:SS" into a DateTime. I then see if NOW is less than that time. if so return todays date, if not return todays date + 1 day.

I hope this helps you out.

All Answers

Bryan JamesBryan James
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 13:00:00" ),TODAY(),TODAY() + 1)

Something like that might work. I am converiting todays year, month, and day by using the TEXT() formula type to concatinate them with a time of 1300 hours so that the DATETIMEVALUE converts the string "YYYY-MM-DD HH:MM:SS" into a DateTime. I then see if NOW is less than that time. if so return todays date, if not return todays date + 1 day.

I hope this helps you out.
This was selected as the best answer
Stephen02Stephen02
Bryan - that is really good. The only issue I see after testing is that the result of the formula shows Date + Time (e.g May 3, 2016 7:00 AM) whereas I only want to have the Date displayed (e.g. May 3, 2016). Maybe a slight trick to this ? Thanks for your help.
Stephen02Stephen02
Thank you, The below expression from Bryan is 90%, but I believe something maybe missing. Can you help me complete the expression with the actual syntax from above, using Value(MID( TEXT( now() - (7/24) ), 12, 2 ))  (note: i'm not sure what the 12 and 2 mean)

IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 13:00:00" ),TODAY(),TODAY() + 1)

Thank you.
mritzimritzi
Give this piece of code a try, and let me know how it functions (The formula field's type should be "Date"):
IF( NOW()  <  DATETIMEVALUE(TODAY())+(13/24) , TODAY(), TODAY()+1)


(Note: Datetimevalue function returns today's date & 12 am GMT ,thus added 13 hours to it , even NOW() return current time in GMT later all these values are converted into User's locale. You can also use CreatedDate field instead of NOW() if that's what you seek.)

Select it as Best Answer, if it works for you.
Stephen02Stephen02
I found that these three variations work.  I am not 100% sure why 12 equates to 1 PM MT, but either way, any of the three (3) case formulas below will work to calculate a return value of 1 PM MT

# 1
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 20:00:00" ),TODAY(),TODAY() + 1)(Note: this is converiting todays year, month, and day by using the TEXT() formula type to concatinate them with a time of 1300 hours so that the DATETIMEVALUE converts the string "YYYY-MM-DD HH:MM:SS" into a DateTime. I then see if NOW is less than that time. if so return todays date, if not return todays date + 1 day.
 
#2
IF( VALUE(LEFT(RIGHT(TEXT(NOW()),9),2)) < 20, TODAY(), TODAY()+1 ) 

#3
IF( NOW()  <  DATETIMEVALUE(TODAY())+(20/24) , TODAY(), TODAY()+1)(Note: Datetimevalue function returns today's date & 12 am GMT ,thus added 13 hours to it , even NOW() return current time in GMT later all these values are converted into User's locale. You can also use CreatedDate field instead of NOW() if that's what you seek.)