You need to sign in to do that
Don't have an account?
Stephen02
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.
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.
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
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.
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 13:00:00" ),TODAY(),TODAY() + 1)
Thank you.
(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.
# 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.)