US Daylight Saving Time Date Calculation in Excel

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 Savings Time Rule. The clocks change at 2 AM Sunday morning. (Source: Me)

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 2: Spring DST Dates.

Figure 3: Fall DST Dates.

Figure 3: Fall DST Dates.

 
This entry was posted in Excel. Bookmark the permalink.

3 Responses to US Daylight Saving Time Date Calculation in Excel

  1. NotACapitalist says:

    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

     
  2. Roger Bye says:

    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?

     

Leave a Reply

Your email address will not be published. Required fields are marked *