Unable to format a timestamp to a date in vba-CodePudding

I'm trying to format a timestamp 1624373961042 to a date but the way I've tried below always gives me overflow error. I was expecting to get this date 2021-06-22 from that timestamp. How can I make it possible?

Sub getDate()
    Dim tDate As Long, timestamp As String
    timestamp = "1624373961042"
    
    tDate = Int(timestamp) / 1000
    MsgBox Format(tDate, "yyyymmdd")
End Sub

CodePudding user response：

You need to divide the number by the number of milliseconds in a day: 86400000

then add the number of days from 12/31/1899 and 1/1/1970: 25569

Sub getDate()
    Dim tDate As Long, timestamp As String
    timestamp = "1624373961042"
    
    tDate = Int((CDbl(timestamp) / 86400000)   25569)
    MsgBox Format(tDate, "yyyymmdd")
End Sub