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
Jesse WolffJesse 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 
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 WolffJesse 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 WolffJesse Wolff
Below 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.
  
IF(
    AND(
        TODAY() >= (DATE(YEAR(TODAY()),3,1+7*2) - WEEKDAY(DATE(YEAR(TODAY()),3,7-0))),
        TODAY() <= (DATE(YEAR(TODAY()),11,1+7*1) - WEEKDAY(DATE(YEAR(TODAY()),11,8-0)))
    ),
    TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&"@"&" "&MID(TEXT(Now()-(4/24)),12,5),
    TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&"@"&" "&MID(TEXT(Now()-(5/24)),12,5)
)

 
Alain CabonAlain Cabon
Hi,

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.
  
IF(
  VALUE( MID( TEXT( NOW() -(4/24) ), 12, 2 ) ) < 12,
  "AM",
  "PM"
)

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 WolffJesse Wolff
Thank 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 WolffJesse Wolff
It'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*
  
IF(
    AND(
        TODAY() >= (DATE(YEAR(TODAY()),3,1+7*2) - WEEKDAY(DATE(YEAR(TODAY()),3,7-0))),
        TODAY() <= (DATE(YEAR(TODAY()),11,1+7*1) - WEEKDAY(DATE(YEAR(TODAY()),11,8-0)))
    ),
		TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&IF(HOUR(TIMENOW())-4 < 12,TEXT(HOUR(TIMENOW())-4), TEXT(HOUR((TIMENOW())-4)-12))&":"&
		IF(
			Minute(timenow())<10,
				"0"&TEXT(Minute(timenow())),
				TEXT(Minute(timenow())))&
		IF(
			HOUR(TIMENOW())-4 < 12,
				" AM",
				" PM"),
		TEXT(MONTH(TODAY()))&"/"&TEXT(DAY(TODAY()))&"/"&TEXT(YEAR(TODAY()))&" "&IF(HOUR(TIMENOW())-5 < 12,TEXT(HOUR(TIMENOW())-5), TEXT(HOUR((TIMENOW())-5)-12))&":"&
		IF(
			Minute(timenow())<10,
				"0"&TEXT(Minute(timenow())),
				TEXT(Minute(timenow())))&
		IF(
			HOUR(TIMENOW())-5 < 12,
				" AM",
				" PM")
)

 
Alain CabonAlain Cabon
Congrats Jesse for the "modernization".

You will often found the conversion 0 for Sunday in formulas indeed but the function WEEKDAY returns also 1 for Sunday in Salesforce.

WEEKDAY: Description: Returns the day of the week for the given date, using 1 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  = 2 or 1
D= DayOfWeek = Sun=1, Mon=2, Tue=3,..., Sat=7

https://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,(2*7+1)) - WEEKDAY(DATE(YEAR(TODAY()),3,8-1))
  • 1st Sunday in November: DATE( YEAR(TODAY()),11,(1*7+1)) - WEEKDAY(DATE(YEAR(TODAY()),11,8-1)) 

Daylight Saving Time (DST) in the USA starts on the 2nd Sunday in March and ends on the 1st Sunday in November. The current schedule was introduced in 2007 and 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 begins the last Sunday of March at 2 o'clock in the morning. One moment, it is added one hour at the legal time.
- the summer time period ends on the last Sunday of October at 3 am At this moment it is cut off for one hour at legal time.

 
Alain CabonAlain Cabon
You 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 WolffJesse Wolff
That is good to know.  I've been tripping over my overthinking brain trying to figure this out.  Will work on it again.
Alain CabonAlain Cabon
You are surely very close to the perfect solution, Jesse.
It is already an impressive formula which works.
Jesse WolffJesse Wolff
Your 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 CabonAlain Cabon
There 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/2018
and 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 WolffJesse Wolff
Ok 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:
  
IF(
	DST_Test_Date__c > DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(DST_Test_Date__c)),3,1+7*2)-WEEKDAY(DATE(YEAR(DATEVALUE(DST_Test_Date__c)),3,8-1)))&" 07:00:00Z") &&
	DST_Test_Date__c < DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(DST_Test_Date__c)),11,1+7*1)-WEEKDAY(DATE(YEAR(DATEVALUE(DST_Test_Date__c)),11,8-1)))&" 06:00:00Z"),
	TEXT(MONTH(DATEVALUE(DST_Test_Date__c)))&"/"&TEXT(DAY(DATEVALUE(DST_Test_Date__c)))&"/"&TEXT(YEAR(DATEVALUE(DST_Test_Date__c)))&" "&
		IF(
			HOUR(TIMEVALUE(DST_Test_Date__c)-14400000) < 12,
				TEXT(HOUR(TIMEVALUE(DST_Test_Date__c)-14400000)),
				TEXT(HOUR((TIMEVALUE(DST_Test_Date__c)-14400000))-12))&":"&
		IF(
			Minute(TIMEVALUE(DST_Test_Date__c))<10,
				"0"&TEXT(Minute(TIMEVALUE(DST_Test_Date__c))),
				TEXT(Minute(TIMEVALUE(DST_Test_Date__c))))&
		IF(
			HOUR(TIMEVALUE(DST_Test_Date__c))-4 < 12,
				" AM",
				" PM"),
	TEXT(MONTH(DATEVALUE(DST_Test_Date__c)))&"/"&TEXT(DAY(DATEVALUE(DST_Test_Date__c)))&"/"&TEXT(YEAR(DATEVALUE(DST_Test_Date__c)))&" "&
		IF(
			HOUR(TIMEVALUE(DST_Test_Date__c))-5 < 12,
			TEXT(HOUR(TIMEVALUE(DST_Test_Date__c))-5),
			TEXT(HOUR((TIMEVALUE(DST_Test_Date__c))-5)-12))&":"&
		IF(
			Minute(TIMEVALUE(DST_Test_Date__c))<10,
				"0"&TEXT(Minute(TIMEVALUE(DST_Test_Date__c))),
				TEXT(Minute(TIMEVALUE(DST_Test_Date__c))))&
		IF(
			HOUR(TIMEVALUE(DST_Test_Date__c))-5 < 12,
				" AM",
				" PM")
)


 
Alain CabonAlain Cabon
Really 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 WolffJesse Wolff
First, 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.
  
IF(
	NOW() > DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(NOW())),3,1+7*2)-WEEKDAY(DATE(YEAR(DATEVALUE(NOW())),3,8-1)))&" 07:00:00Z") &&
	NOW() < DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(NOW())),11,1+7*1)-WEEKDAY(DATE(YEAR(DATEVALUE(NOW())),11,8-1)))&" 06:00:00Z"),

	TEXT(MONTH(DATEVALUE(NOW())))&"/"&TEXT(DAY(DATEVALUE(NOW())))&"/"&TEXT(YEAR(DATEVALUE(NOW())))&" "&
		IF(
			HOUR(TIMEVALUE(NOW())-14400000) < 12,
				TEXT(HOUR(TIMEVALUE(NOW())-14400000)),
				TEXT(HOUR((TIMEVALUE(NOW())-14400000))-12))&":"&
		IF(
			Minute(TIMEVALUE(NOW()))<10,
				"0"&TEXT(Minute(TIMEVALUE(NOW()))),
				TEXT(Minute(TIMEVALUE(NOW()))))&
		IF(
			HOUR(TIMEVALUE(NOW()))-14400000 < 12,
				" AM",
				" PM"),

	TEXT(MONTH(DATEVALUE(NOW())))&"/"&TEXT(DAY(DATEVALUE(NOW())))&"/"&TEXT(YEAR(DATEVALUE(NOW())))&" "&
		IF(
			HOUR(TIMEVALUE(NOW()))-18000000 < 12,
			TEXT(HOUR(TIMEVALUE(NOW()))-18000000),
			TEXT(HOUR((TIMEVALUE(NOW()))-18000000)-12))&":"&
		IF(
			Minute(TIMEVALUE(NOW()))<10,
				"0"&TEXT(Minute(TIMEVALUE(NOW()))),
				TEXT(Minute(TIMEVALUE(NOW()))))&
		IF(
			HOUR(TIMEVALUE(NOW()))-18000000 < 12,
				" AM",
				" PM")
)

/* The First part checks if NOW() is AFTER 02:00L on the 2nd Sunday in March and BEFORE 02:00L on the 1st Sunday in November.  Time is hard coded to a zulu offset based on your region and what time of day you want the change to occur. */
/* DATETIME > DATETIMEVALUE(DATE(YEAR,MONTH,1+7*n) - WEEKDAY(DATE(YEAR,MONTH,8-xday))&" "& Zulu time formatted HH:MM:SSZ to your regional offset and time desired */
/* The next part formats NOW() into a text value of "M/DD/YYYY H:MM AM/PM" in 12H time. Depending on if NOW() falls within DST (March thru November) or outside (November thru March) */
/* Then subtracts (or adds) hours in milliseconds and converts from 24h time to 12h time */

 
MarkLMarkL
Using 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
MarkLMarkL
I 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
  
HOUR(TIMEVALUE(NOW())-18000000) < 12,
" AM",
 " PM")

 
MarkLMarkL
Updated code, seems to work (hopefully) fixed all instances of the -18000000 and -14400000 being outside of parenthesis when they should have been inside parenthesis:
  
IF(
	NOW() > DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(NOW())),3,1+7*2)-WEEKDAY(DATE(YEAR(DATEVALUE(NOW())),3,8-1)))&" 07:00:00Z") &&
	NOW() < DATETIMEVALUE(TEXT(DATE(YEAR(DATEVALUE(NOW())),11,1+7*1)-WEEKDAY(DATE(YEAR(DATEVALUE(NOW())),11,8-1)))&" 06:00:00Z"),

	TEXT(MONTH(DATEVALUE(NOW())))&"/"&TEXT(DAY(DATEVALUE(NOW())))&"/"&TEXT(YEAR(DATEVALUE(NOW())))&" "&
		IF(
			HOUR(TIMEVALUE(NOW())-14400000) < 12,
				TEXT(HOUR(TIMEVALUE(NOW())-14400000)),
				TEXT(HOUR((TIMEVALUE(NOW())-14400000))-12))&":"&
		IF(
			Minute(TIMEVALUE(NOW()))<10,
				"0"&TEXT(Minute(TIMEVALUE(NOW()))),
				TEXT(Minute(TIMEVALUE(NOW()))))&
		IF(
			HOUR(TIMEVALUE(NOW())-14400000) < 12,
				" AM",
				" PM"),

	TEXT(MONTH(DATEVALUE(NOW())))&"/"&TEXT(DAY(DATEVALUE(NOW())))&"/"&TEXT(YEAR(DATEVALUE(NOW())))&" "&
		IF(
			HOUR(TIMEVALUE(NOW())-18000000) < 12,
			TEXT(HOUR(TIMEVALUE(NOW())-18000000)),
			TEXT(HOUR((TIMEVALUE(NOW())-18000000))-12))&":"&
		IF(
			Minute(TIMEVALUE(NOW()))<10,
				"0"&TEXT(Minute(TIMEVALUE(NOW()))),
				TEXT(Minute(TIMEVALUE(NOW()))))&
		IF(
			HOUR(TIMEVALUE(NOW())-18000000) < 12,
				" AM",
				" PM")
)

/* The First part checks if NOW() is AFTER 02:00L on the 2nd Sunday in March and BEFORE 02:00L on the 1st Sunday in November.  Time is hard coded to a zulu offset based on your region and what time of day you want the change to occur. */
/* DATETIME > DATETIMEVALUE(DATE(YEAR,MONTH,1+7*n) - WEEKDAY(DATE(YEAR,MONTH,8-xday))&" "& Zulu time formatted HH:MM:SSZ to your regional offset and time desired */
/* The next part formats NOW() into a text value of "M/DD/YYYY H:MM AM/PM" in 12H time. Depending on if NOW() falls within DST (March thru November) or outside (November thru March) */
/* Then subtracts (or adds) hours in milliseconds and converts from 24h time to 12h time */

 
Jesse WolffJesse Wolff
Fantastic!  Did this resolve your AM/PM discrepancy?  Are your overnight hours displaying correctly?
MarkLMarkL
I'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 WolffJesse Wolff
Awesome.  I'll install the new code and see if I get similar results.