0

I have a csv file with a date column and I am having trouble trying to figure out the format in which this date is in.

enter image description here

I have tried several timestamp converters and none of them seem to give me the accurate date.

The dates should all range within 2017.

halfer
  • 19,824
  • 17
  • 99
  • 186
Kimkykie
  • 49
  • 6
  • 2
    `DateTime.FromOADate(42954.49986111111);` -> 2017-08-07T11:59:48.0000000. See https://stackoverflow.com/questions/4538321/reading-datetime-value-from-excel-sheet for more info – phuzi Oct 04 '19 at 08:38

3 Answers3

2

Excel store dates and times as a floating point number as days since 1900/01/01 00:00:00. So 42954.49986111111 represents 2017-08-07 11:59:48.0000000.

.Net can already understand this "format":

DateTime.FromOADate(42954.49986111111);
phuzi
  • 12,078
  • 3
  • 26
  • 50
1

Looks like this is days from 1900, so to get timestamp you should add this value to 1900/01/01 timestamp multiplying on 1000 * 24 * 60 * 60

It should work

const d = Date.UTC(1900, 1, 1) + 24 * 3600 * 1000 * 42982
console.log(new Date(d).toString())
Dmitry Reutov
  • 2,995
  • 1
  • 5
  • 20
  • 3
    Or just `DateTime.FromOADate()` – phuzi Oct 04 '19 at 08:38
  • @phuzi I didnt know this method, of course it is shorter – Dmitry Reutov Oct 04 '19 at 08:51
  • Beware that this method will not work for dates before 30th December 1899. The OA date number represents days for the integer part and fractional part of a day for the decimal part. i.e 3.5 would be 3 days and 12 hours after 30th December 1899. When applied to negative numbers the negative only applies to the integer part. So -2.5 means 2 days before 30th December 1899 plus half a day, meaning it's only 1.5 days earlier not 2.5 days. – Tim Andrews Oct 04 '19 at 09:05
0

I think it is any date formate.

moment(42954.4998611111).format('MMMM Do YYYY, h:mm:ss a')
"January 1st 1970, 5:30:42 am"
Ankit Kumar Rajpoot
  • 5,188
  • 2
  • 38
  • 32