date timemicrosoft excel
I have a spreadsheet containing several timestamp columns, given in Unix time, i.e. number of seconds since 01/01/1970 00:00.00 UTC.
Using Excel 2010, are there any methods, and if so how, to convert these timestamps into a date/time format recognizable by Excel?
This will be a little convoluted but I think it should work. Note, I am using Excel 2007 for this, so some steps may be a little different.
After importing your file (post step 9 above) perform the following:
=TIME(0,F2,G2) (where column F is the Duration column, column G is the first new column and row 2 is the first row beneath the headers)This should produce output similar to thus:
I agree with Excellll; you're going to need VBA to convert the values in place to a string value.
This method won't result to text strings; but it might help you get the format you need.
Let's say these were my minutes:
Type 1440 into any blank cell. Select this cell and press Ctrl + C.
Select the range that contains the minutes.
Paste Special > Values > Divide
Click OK. Result:
Select the range of number again and press Ctrl + 1.
Go to Number > Custom
Type the format:
[h] "h" mm "m"
Result:
If you'd like to change them back to minutes:
1440 into any blank cell. Select this cell and press Ctrl + C.To get the number of hours in decimal form, use 60 instead of 1440. So for 330 (mins), you'll get 5.5 (hours).
Best Answer
Try:
Division by 86400 is needed to convert it into days (product of 24 * 60 * 60), you then add the number of days between 1st Jan 1900 (which is where Excel bases its dates off) and 1st of Jan 1970.