(Solved) – How to convert epoch dates across years, including DST, in Excel using VBA Script

(solved)-–-how-to-convert-epoch-dates-across-years,-including-dst,-in-excel-using-vba-script

I have a spreadsheet that includes a “Last Modified Date” in epoch format in column C, such as:

1486841495 (earliest date in 2017)
1574478516 (latest date in 2019)

Column C has 6,003 rows. What I want to do is have a script take what’s in column C and convert it to the following format in column E: MM/DD/YY HH:MM:SS AM/PM. I have the result cells formatted correctly so that’s showing up right. I’m in Central time zone.

I have very little exposure to writing code and I’ve been trying to piece something together for hours.

This is the closest I’ve been able to get, but it only runs on one cell and I need it to run on the entire column C. Can anyone help?

Sub CalcDate()

‘2017

If Range("C2").Value > 1483250400 And Range("C2").Value < 1489298520 Then
Range("E2:E10").Value = ((Range("C2:E10") - 21600) / 86400)   25569

ElseIf Range("C2").Value > 1489298520 And Range("C2").Value < 1509858120 Then
Range("E2").Value = ((Range("C2") - 18000) / 86400)   25569

ElseIf Range("C2").Value > 1509858120 And Range("C2").Value < 1514743199 Then
Range("E2").Value = ((Range("C2") - 21600) / 86400)   25569

'2018

ElseIf Range("C2").Value > 1514786400 And Range("C2").Value < 1520755200 Then
Range("E2:E10").Value = ((Range("C2:E10") - 21600) / 86400)   25569

ElseIf Range("C2").Value > 1520755200 And Range("C2").Value < 1541318400 Then
Range("E2").Value = ((Range("C2") - 18000) / 86400)   25569

ElseIf Range("C2").Value > 1541318400 And Range("C2").Value < 1546279199 Then
Range("E2").Value = ((Range("C2") - 21600) / 86400)   25569

'2019

ElseIf Range("C2").Value > 1546322400 And Range("C2").Value < 1552204800 Then
Range("E2:E10").Value = ((Range("C2:E10") - 21600) / 86400)   25569

ElseIf Range("C2").Value > 1552204800 And Range("C2").Value < 1572768000 Then
Range("E2").Value = ((Range("C2") - 18000) / 86400)   25569

ElseIf Range("C2").Value > 1572768000 And Range("C2").Value < 1577815199 Then
Range("E2").Value = ((Range("C2") - 21600) / 86400)   25569

End If
End Sub

Leave a Reply

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