Quote of the Day
There is a wonderful, almost mystical, law of nature that says three of the things we want most—happiness, freedom, and peace of mind—are always attained when we give them to others. Give it away to get it back.
— Basketball coach John Wooden
I recently had a situation where I needed to correct a number of date/time values because they did not take into account Daylight Saving Time (DST). To be specific, some transactions from China were recorded assuming a fixed time offset with respect to US Central Standard Time. Because of DST, this is not always the case. My customer only works in Excel, so the work was done in Excel.
To perform this correction, I need to know if the date/time values occurred during the DST date ranges for those years. As discussed below, the US adopted its current DST assignments in 2005. I only need to calculate the DST correction for dates after 2005, so I will not worry about earlier years.
The 2005 Energy Policy Act defined the dates of DST to be:
- Start DST: Second Sunday of March.
- End DST: First Sunday in November.
Solving this problem requires two Excel date functions.
- DATE(year, month, date)
- This function computes an Excel date object, which is an integer equal to the number of days since January 1st, 1900.
- WEEKDAY(date object)
- This function takes a date object and computes an integer that corresponds to the day of the week, where the default numbering is Monday = 1, Tuesday = 2, … , Sunday = 7.
Date Calculation Approach
I will illustrate the calculation using the 2021 Spring DST date calculation.
- Create a date object for the latest possible spring DST date (14-March-2021)
- If March 1st is a Monday, the second Sunday in March is on the 14th
- Excel Calculation: latest = DATE(2021, 3, 14)
- Compute the weekday of 14-March-2021 from the previous Sunday
- This is simply the weekday number of 14-March-2021 minus 1
- offset = WEEKDAY(latest) – 1 (Sunday = 0, Monday = 1, …, Saturday = 6)
- SpringDST = latest -offset = DATE(2021, 3, 14) – (WEEKDAY(DATE(2021,3,14))-1)
The same logic can be used to give us the Fall DST date by substituting in the lastest possible first Sunday in November: DATE(2021, 11, 7) – (WEEKDAY(DATE(2021,11,7)-1) = 7-Nov-2021.