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
Introduction
Figure 1: US Daylight Saving Time Rule: Spring Ahead, Fall Back. The clocks change at 2 AM Sunday morning. (Source: Me)
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.
Background
Date Definitions
The 2005 Energy Policy Act defined the dates of DST to be:
- Start DST: Second Sunday of March.
- End DST: First Sunday in November.
Excel Dates
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.
Analysis
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)
=14-Mar-2021
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.
Excel Workbook Example
My Excel workbook can be found here. I used it to create the following tables (Figures 2 and 3) of Spring and Fall DST dates, which agree with a similar online table.
Figure 2: Spring DST Dates.
Figure 3: Fall DST Dates.
DST becomes even more complex in the State of Arizona. On the Res (Fed./treaty influences) changes may occur. AZ, where the state on MST, Mountain Stranded Time. [Time Ref: https://gulchradio.com/%5D
A small correction for the spelling-zealous among us.
The word "S" stands for in "DST" has only one "S"; it is "Saving", not "Savings".
Almost everyone gets it wrong, but we enjoy being among those who do get it right, don't we?
Darn. I knew that and still made the error. I really appreciate the help. Thank you!
mark
I have attempted to find some VBA code that will allow me to analyze a data extract that contains some values that were captured at a specific date and time in UTC (hh:mm:ss).
I need to identify any data cells in UTC date-time format that fall within the date-time criteria for Pacific Daylight Time and then modify and store the PDT date-time value into an adjacent cell in the same row as the UTC date-time cell.
There seems to be many VBA routines that do this based solely on the Date value of the cell being analyzed. Since Daylight Saving Time or Standard Time dos not occur until 2:00am of a specific day, the above code shown in this blog don't handle data that is between 00:00:00hrs and 01:59:59hrs on the date that we switch from Standard Time to Daylight Time or Daylight Time to Standard Time. That slice of data should not be included in the modification just because it has the correct date.
I'm like others who read these blogs and forums in that I am just now beginning to learn Excel VBA and am not a programmer but more of a hobbiest.
Thanks for any help and/or pointers you may have to clarify my perception of this issue.
You can use two simple formulas:
For example, to convert UTC to EST, if your date is in A1, for example, then in A2 (for example) enter:
IF(MATCH(A1,DATE(YEAR(A1),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(A1),{1,3,11},7)))=2,"DST","EST")
and in A3 do:
=IF(A2="DST",A1+TIME(4,0,0),A1+TIME(5,0,0))
I was too quick in writing my comment... The addition of hours is done to a cell containing time and not to a date...