How to determine if date occurred inside Day Light Savings time in Excel.

Related Questions

  • Why are the links in my Exception Detail with Trips History Report not working

Applies To

  • MyGeotab
  • Reports
  • Custom Reports

Procedure

  1. Find the cell which contains the Time stamp
  2. Create 8 new columns inside the Reports tab
  3. Label the 8 columns the following (Year, DST1, Days to DST1, DST1 Date, DST2, Days to DST2, DST2 Date, IsDST)
  4. For each column, using the following formula:

Replace Bolded section with the Cell. Example B4

Year: 

=TEXT(<Cell which contains Timestamp>,"yyyy")

DST1:

=WEEKDAY(DATE(<Cell of Year>,3,8),1)

Days to DST1:

=IF(<Cell of DST1> = 1,0,8-<Cell of DST1>)

DST1 Date

=DATE(<Cell of Year>,3,8+<Days to DST1>) + 2/24

DST2:

=WEEKDAY(DATE(<Cell of Year>,11,1),1)

Days to DST2:

=IF(<Cell of DST2> = 1,0,8-<Cell of DST2>)

DST2 Date

=DATE(<Cell of Year>,3,8+<Days to DST2>) + 2/24

IsDST 

=IF(AND(<Cell of Time> > <Cell of DST1 Date>,<Cell of Time> < <Cell of DST2 Date>),TRUE,FALSE)

Use the result in IsDST to count if Timestamp is inside DST and make the adjustment accordingly.

For example in EST time, if IsDST is TRUE, we would use 5 hours (5/24 in excel). If IsDST is FALSE, we would use 4 hours (4/24 in excel)

Additional Notes

Example: 

inline-1656051736.png

Year: =TEXT(I11,"yyyy")
DST1: =WEEKDAY(DATE(J11,3,8),1)
Days to DST1: =IF(K11=1,0,8-K11)
DST1 Date: =DATE(J11,3,8+L11)+2/24
DST2: =WEEKDAY(DATE(J11,11,1),1)
Days to DST2: =IF(N11=1,0,8-N11)
DST2 Date: =DATE(J11,11,1+O11)+2/24
DST?: =IF(AND(I11>M11,I11<P11),TRUE,FALSE)