1

I got array of dates from excel file and they look like strange fractional numbers: 42425.4166550926, 42419.3326041667, 42411.5419097222, etc. How to convert them to unix timestamp or javascript date?

fourslashw
  • 899
  • 1
  • 8
  • 8
  • 1
    Without more information it will be very difficult to provide an answer. Where did these timestamps get generated? Do you know what the resulting date *should* be? – duncanhall Apr 20 '16 at 14:37
  • 1
    Does anything [here](http://www.mrexcel.com/forum/excel-questions/664812-visual-basic-applications-convert-timestamp.html) help? What have you tried? – BruceWayne Apr 20 '16 at 14:44
  • I think this is excel raw date format. LibreOffice Calc gives the same numbers with "@" cell formatting. As for resulting values - yes, I know them: 42379.4806597222 is 01/10/2016 11:32:09 for example. As a last resort I could just format all collumns as text in xlsx file and parse it again, but I wonder is there other way around. – fourslashw Apr 20 '16 at 14:48
  • 1
    for a js solution, there is a conversion function for that posted by someone [on gist](https://gist.github.com/christopherscott/2782634) – michaPau Apr 20 '16 at 14:55

1 Answers1

2

To convert date to timestamp, a formula can work it out.

Select a blank cell and type this formula =(A1-DATE(1970,1,1))*86400 into it and press Enter key, if you need, you can apply a range with this formula by dragging the autofill handle. Now a range of date cells have been converted to Unix timestamps.

enter image description here

Source

Another source...

Community
  • 1
  • 1
Elbert Villarreal
  • 1,696
  • 1
  • 11
  • 22