convert Excel decimal date time into time.Time-CodePudding

While parsing an excel file in Golang, a date time column is getting read as 44531.553587963 which actually represents the time 12/01/2021 13:17:10.

How can I convert this decimal representation to time.Time object in go?

CodePudding user response：

var in float64 = 44531.553587963
excelEpoch := time.Date(1899, 12, 30, 0, 0, 0, 0, time.UTC)
tm := excelEpoch.Add(time.Duration(in * float64(24 * time.Hour)))
fmt.Println(tm) // 2021-12-01 13:17:10.000003072  0000 UTC

Excel time is (usually, barring weird legacy versions of the format) measured in days since 12/30/1899. It was meant to be so that 1.0 would equal midnight at 1900-01-01, but someone used a faulty leap year algorithm that thought that February 1900 had 29 days, when it only had 28, so all dates after that point were off-by-1. Rather than break existing spreadsheets, they left that bug in place, so to get correct dates (post-1900) you have to offset the epoch back by one day, so 1.0 equals 1989-12-31 and 0.0 equals 1989-12-30.

Anyway, once we know the epoch it's just a little type juggling to convert one day in nanoseconds to a float, multiply, and then convert back to a duration, and add that duration to the epoch.