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.