You need to sign in to do that

Don't have an account?

Jesse Wolff

# Daylight Savings Time Formula

Team I have a 2 part question:

I have a formula field with TEXT output in order to use Now() in other text fields (ie "10/29/2018 @ 12:00:00 the field was updated"). The only requirement is that the field display the time in Eastern Time taking into account DST. Until today I have been updating the formula twice per year and thought I'd revisit the requirement after several SF updates, and I've come up with (what I think is) a pretty good formula (see below). I would like input on the following:

1. Tell me how this is wrong (if at all). I can't see any reason why it would break, but I'm not confident it won't). The true and false values are the original formulas I would manually switch each March and November.

2. If anyone can tell me how to (code snippet) format the date time "MM/DD/YYYY HH:MM AM/PM", that would be awesome (I am missing the AM/PM piece).

Field Type: Formula

Return Type: Text

I have a formula field with TEXT output in order to use Now() in other text fields (ie "10/29/2018 @ 12:00:00 the field was updated"). The only requirement is that the field display the time in Eastern Time taking into account DST. Until today I have been updating the formula twice per year and thought I'd revisit the requirement after several SF updates, and I've come up with (what I think is) a pretty good formula (see below). I would like input on the following:

1. Tell me how this is wrong (if at all). I can't see any reason why it would break, but I'm not confident it won't). The true and false values are the original formulas I would manually switch each March and November.

2. If anyone can tell me how to (code snippet) format the date time "MM/DD/YYYY HH:MM AM/PM", that would be awesome (I am missing the AM/PM piece).

Field Type: Formula

Return Type: Text

IF( AND( TODAY() > (DATE(YEAR(TODAY()),3,0+7*1) - WEEKDAY(DATE(YEAR(TODAY()),3,8-1))), TODAY() < (DATE(YEAR(TODAY()),11,0+7*2) - WEEKDAY(DATE(YEAR(TODAY()),11,8-1))) ), TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&"@"&" "&MID(TEXT(Now()-(4/24)),12,8), TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&"@"&" "&MID(TEXT(Now()-(5/24)),12,8) )

Jesse Wolff**Note - I may have some of the values wrong for the day numbers. I converted them from Excel formulas. If you see an error please let me know. Jesse WolffBelow is a working edit. It works at least through 2020. If anyone can explain the difference between the March and November formulas, it's got me a bit confused as to why it needs to be different.

Alain CabonHi,

You have built great formulas. That is one of the most difficult problems with formulas.

Your technique of formatting is standard and common with formulas.

https://developer.salesforce.com/docs/atlas.en-us.usefulFormulaFields.meta/usefulFormulaFields/formula_examples_dates.htm

The date are always saved in GMT.

For the fully explanation of the formula, I need more time ( WEEK DAY ?) and not sure to understand all the details easily.

-(4/24) or -(5/24) = is the time offset = GMT time minus four or five hours.

Jesse WolffThank you for the AM/PM syntax and the links! Very helpful!

Basically, the WEEKDAY formula SHOULD say (and what I'm trying to verify):

IF,

TODAY >= (The 2nd Sunday in March of Today's year) AND

TODAY <= (The 1st Sunday in Novevmber of Today's year)

THEN format the dates accordingly.

If you are interested, the Excel formulas were found at the links below. Fairly straight forward, but I had to modify due to differences between Excel and Salesforce (ie Excel is Sunday (1) thru Saturday (7) while Salesforce is Sunday (0) thru Saturday (6)). I'm not 100% sure I converted properly, but it works through 2020.

http://www.excelforum.com/tips-and-tutorials/398194-calculating-the-date-of-the-nth-specific-day-of-the-month.html#post1426804

https://superuser.com/questions/359585/what-excel-formula-can-i-use-to-calculate-the-2nd-monday-of-a-given-month

Jesse WolffIt's kind of a beast, but I ran with it. lol Here's modified code for formatting date/time as "MM/DD/YYYY HH:MM AM/PM". I'm taking advantage of SF new time functions.

*Sorry for the repost, I had to fix sample fields in my formulas, and reformatted for easier viewing*

Alain CabonCongrats Jesse for the "modernization".

You will often found the conversion

0forSundayin formulas indeed but the functionWEEKDAYreturns also1 for Sundayin Salesforce.WEEKDAY:Description: Returns the day of the week for the given date, using1 for Sunday,2 for Monday, through 7 for Saturday.https://help.salesforce.com/articleView?id=customize_functions_i_z.htm&type=5

Salesforce don't give access to the time offset (-4 - 5) but that is not your major problem.

The big complication is this part:

2nd Sunday in March:TODAY() >= (DATE(YEAR(TODAY()),3,1+7*2) - WEEKDAY(DATE(YEAR(TODAY()),3,7-0))),1st Sunday in November:TODAY() <= (DATE(YEAR(TODAY()),11,1+7*1) - WEEKDAY(DATE(YEAR(TODAY()),11,8-0)))DATE(Y,M,(N*7+1))-WEEKDAY(DATE(Y,M,

8-D))or DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday)) as you found.

Y = Year = YEAR(TODAY())

M = Month = March = 3 or 11

N = NthDay =

or21D= DayOfWeek =

Sun=1,Mon=2, Tue=3,..., Sat=7https://excelsemipro.com/2010/12/calculate-the-xth-weekday-of-any-month-in-excel/

https://chandoo.org/wp/findout-thanksgiving-date/

2nd Sunday in March:DATE( YEAR(TODAY()),3,(*7+1)) - WEEKDAY(DATE(YEAR(TODAY()),3,8-1))21st Sunday in November:DATE( YEAR(TODAY()),11,(*7+1)) - WEEKDAY(DATE(YEAR(TODAY()),11,18-1))Daylight Saving Time (DST)in the USAstartson the2nd Sunday in Marchandendson the1st Sunday in November.The current schedule was introduced in2007and follows the Energy Policy Act of 2005.https://www.timeanddate.com/time/change/usa

In France, we don't use the same rules.

- the summer time period

beginsthelast Sunday of Marchat 2 o'clock in the morning. One moment, it is added one hour at the legal time.- the summer time period

endson thelast Sunday of Octoberat 3 am At this moment it is cut off for one hour at legal time.Alain CabonYou will often find the conversion 0 for Sunday in formulas indeed but the function WEEKDAY returns also 1 for Sunday in Salesforce. (sorry for my broken english)

Jesse WolffThat is good to know. I've been tripping over my overthinking brain trying to figure this out. Will work on it again. Alain CabonYou are surely very close to the perfect solution, Jesse. It is already an impressive formula which works.

Jesse WolffYour explanation is perfect. Now thinking our time change is also at 2am on both the March and November dates. I don't normally worry about it since my organization has standard business hours. This may be an issue for those with 24/7 operations. How important do you think it is to have the time change occur exactly at 0200? Alain CabonThere is a problem of accuracy between 0h00 and 2h00 indeed.

This formula is a displayed date (text) on screen during business hours ( 8h30 AM- 5h30 PM ) Or also used for batch processes at night?

Globally, if you want a prefect accuracy, there is also a problem to fix for the exact day when you remove enough hours to switch to the previous day.

The saved time: 3h AM GMT, 10/

30/2018 is in fact 11h PM local time (if GMT-4) of the previous day: 10/29/2018and you risk to show: 11h PM, 10/

30/2018 instead of 10/29.The formulas are usefull when they are displayed on screen but for batch processes running at night or in the very early morning they could be totally wrong.

We don't use AM / PM in France.

Jesse WolffOk so I confirmed that overnight weird things happen, including negative times.

To fix this, if I assume formulas result in zulu or GMT and 24h time, (example: Run at 00:00 local time, "HOUR(NOW())" should result in 4 since my local time is GMT -4 currently, but run in Paris, it would result in 23 (or 11) because Paris is GMT +1. I think this means the TIMEVALUE factor will need to be regionally set.

If this assumption is correct, the current formula will effect the change at MIDNIGHT GMT on the 2nd Sunday in March and 1st Sunday in November. That would change 19:00 to 20:00 the day before in March and 20:00 to 19:00 the day before in November.

Anyway - This is revised formula. Working pretty good right now:

Alain CabonReally impressive, Jesse. Congrats.

You understand why I said it is one of the most difficult problems to solve with a formula in Salesforce and you're going to be successful (if not already).

The accuracy with formula using dates and complex rules like the Daylight Saving Time (DST) is a big challenge if the hours are also taken into account (really complicated with the constraints of the limited choice of functions of Salesforce and the limited compiled size, you can have the solution but you cannot write it because you go over the limit of 5000 char.).

I am not sure to test all the cases but I am interesting to see what the results are with the same timezone as yours (GMT-4 or -5).

Best regards

Alain

Jesse WolffFirst, thank you for all your input. It has been extremely helpful in working this out.

I do understand that this is extremely challenging and will not satisfy all use cases, but I think it's in a good place. I've cleaned it up and will run this formula in my org. I'll update again if anything breaks, but I think it's pretty stable now. I've added comments to explain what it's doing.

MarkLUsing your final formula I'm getting AM being displayed when it should be PM. If I reverse " AM" and " PM" it still returns AM

This is for the date/time 2:05pm EDT (eastern daylight time) 10/28/2019

MarkLI think if you change that block to this it is better, but I'm not sure it's still quite right because it shows the hour as 13 when really it should be 14 right now

MarkLUpdated code, seems to work (hopefully) fixed all instances of the -18000000 and -14400000 being outside of parenthesis when they should have been inside parenthesis:

Jesse WolffFantastic! Did this resolve your AM/PM discrepancy? Are your overnight hours displaying correctly? MarkLI've only checked over a few test cases, but so far yes the updated code fixes the AM/PM issues and times seem to be displaying properly. Jesse WolffAwesome. I'll install the new code and see if I get similar results.