1

I have written this code to convert timestamp to date. But when the timestamp value is 2613441599, it throws an overflow error. Any idea as to what the root of the problem is? Thanks in advance

Sub Update_Date()

Dim d_date As Double
Dim x As Long
Dim y As Long
Dim Userlastcol As Long
Dim Userlastrow As Long
Dim sheeta As Worksheet

Set sheeta = ThisWorkbook.Worksheets("UserData")

Userlastrow = sheeta.Range("A" & Rows.Count).End(xlUp).Row
Userlastcol = sheeta.Cells(1, Columns.Count).End(xlToLeft).Column

For x = 1 to Userlastcol
   If sheeta.Cells(1,x).Value = "UpdatedAt" Then
      For y=2 to Userlastrow
         if sheeta.Cells(y,x).Value = 0 Then
            sheeta.Cells(y,x).Value = ""
         else
            d_date = sheeta.Cells(y,x).Value '//fetch timestamp value and store in d_date of data type double
            sheeta.Cells(y,x).Value = DateAdd("s", d_date, "1/1/1970 00:00:00") '//throws overflow error
         End If
      Next y
  End If
Next x
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Sannidhi A
  • 11
  • 2

1 Answers1

1

Looks like the DateAdd function cannot process such a big number of seconds 2613441599.

I recommend to convert the timestamp seconds into days:

d_date  'seconds
d_date / 60  'minutes
d_date / 60 / 60  ' hours
d_date / 60 / 60 / 24  ' days

so 60*60*24 = 86400 to convert seconds into days we need to devide by 86400 to add days "d" in the DateAdd function:

DateAdd("d", d_date / 86400, DateSerial(1970, 1, 1))

Further note that "1/1/1970 00:00:00" is a string and not a date. Yes VBA tries to implicitly convert that into a date but that is not very reliable because it might depend on the date format your operating system is set to. Never use strings as dates. Instead use DateSerial to produce a real numeric date.

The following example will output 2052-10-25.

Option Explicit

Public Sub Example()
    Dim d_date As Double
    d_date = 2613441599#
    
    Debug.Print DateAdd("d", d_date / 86400, DateSerial(1970, 1, 1))
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73