How to convert a date string in Excel to a unix timestamp
You may be wondering why you'd need to do this? Sometimes a client supplies a huge Excel file which they need converting into nodes or importing into a database. It can often be quicker to do the preparation in the spreadsheet rather than try to do convert things in the database. In my situation I needed to import lots of data into Drupal nodes.
To convert a date (e.g. 15/02/2008) to a unix timestamp (e.g. 1235952000) in Excel or Numbers you can do the following:
Lets suppose you have your dates in column A and you want your timestamps in column B. Enter this formula into column B:
=(STRIPDURATION((A3-DATE(1970,1,1)))*86400)
That assumes that the first date is in column A, row 3. Change that part of the formula to suit your data. Next pull that formula down to cover the rest of the rows. In Numbers you just grab the bottom right corner of the cell with the formula in and drag down, I think Excel is the same.
There you have it, nice and easy. Sometimes you may want to delete column A in which case I generally copy column B and paste it into a new column C using the 'Paste as Values' option. Then I delete columns A & B leaving me with my timestamp values.
Open Office? Wassat? In
Open Office? Wassat?
In reverse it would be (assuming column A row 3 is a timestamp):
=(A3/86400)+DATE(1970,1,1)
I haven't tested this though...
Oh and I forgot to mention that the conversion in the article and the one above will produce GMT times so adjust to suit your timezone (or make the adjustments in your web app like I did).
How do I calculate a whole
How do I calculate a whole column of timestamps then?
I have got a huge XML file with thousands of timestamps, (listed in the format: "14-06-2009 16:21:01", "24-02-2007 19:25:10", etc.) that I want to convert to unix time, pasted in another column, instead of entering a formula for every each of the timestamps, one at a time. That would be the death.
Regards, Thomas
Thomas, you should be able to
Thomas, you should be able to copy and paste the cell containing the formula and it will update the formula in each cell.
Here some useful advice
Here some useful advice provided by dpw (thanks!):
I couldn't find the STRIPDURATION function in OpenOffice, so here in an equivalent that worked for me...
=(DAYS(A2,(DATE(1970,1,1)))*86400)
Note this is GMT, so you'd need to add or subtract additional seconds to correct for your timezone.
How about the reverse? But in
How about the reverse? But in Open Office please :)