Apex Code Development You need to sign in to do that
Don't have an account?
Stephen02 Date formula that calculates a today or tomorrow date, based on GMT Time
Hi - I am working with the three Date formulas below (all of which do the same thing) and that is it checks to see if the current time is before 1 PM MT, and if so, then show a resulting date of today (e.g. May 3, 2016). If it is after 1 PM MT, then show a resulting date of tomorrow (e.g. May 4, 2016). Now I have to expand the formula to be a bit more intelligent.
With that said, I have users in different locals/timezones and I am asking for a formula that calculates the following (note I do prefer Formula 3 below, but am open to whatever recommendations work for the requirement below:)
Requirements:
* IF GMT +00:00, then calculated result is today (e.g. May 3, 2016) and
* IF GMT +01:00, then result is today (e.g. May 3, 2016) and
* IF GMT +02:00, then result is today (e.g. May 3, 2016) and
* IF GMT +03:00, then result is today (e.g. May 3, 2016) and
* IF GMT +04:00, then result is today (e.g. May 3, 2016) and
* IF GMT +05:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT +06:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT -01:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT -02:00, then result is tomorrow (e.g. May 4, 2016) and
These working formulas can be used for reference and/or to add on to
#1)
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 20:00:00" ),TODAY(),TODAY() + 1)
(Note: this is converting todays year, month, and day by using the TEXT() formula type to concatinate them with a time of 2000 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 20 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.)
Thank you!
With that said, I have users in different locals/timezones and I am asking for a formula that calculates the following (note I do prefer Formula 3 below, but am open to whatever recommendations work for the requirement below:)
Requirements:
* IF GMT +00:00, then calculated result is today (e.g. May 3, 2016) and
* IF GMT +01:00, then result is today (e.g. May 3, 2016) and
* IF GMT +02:00, then result is today (e.g. May 3, 2016) and
* IF GMT +03:00, then result is today (e.g. May 3, 2016) and
* IF GMT +04:00, then result is today (e.g. May 3, 2016) and
* IF GMT +05:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT +06:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT -01:00, then result is tomorrow (e.g. May 4, 2016) and
* IF GMT -02:00, then result is tomorrow (e.g. May 4, 2016) and
These working formulas can be used for reference and/or to add on to
#1)
IF(NOW() < DATETIMEVALUE(TEXT(YEAR(TODAY()))&"-"&TEXT(MONTH(TODAY()))&"-"&TEXT(DAY(TODAY()))&" 20:00:00" ),TODAY(),TODAY() + 1)
(Note: this is converting todays year, month, and day by using the TEXT() formula type to concatinate them with a time of 2000 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 20 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.)
Thank you!