How to get yr, month, day, hr, min and s stored as separate variables from a serial date number?

I have a vector of serial date numbers (such as 37987.0108217593) and I need to convert each one into year, month, day hr, min second. Example 37987.0108217593 would be year=2004, month=1, day=2, hour=0, min=29 and seconds=20. So I would have a vector called year, a vector called month etc.

Any help will be greatly appreciated!

Answer 1

[Steven Lord]

That's close to what you get using the Excel datetime type, but not quite.

>> D = datetime( 37987.0108217593, 'ConvertFrom', 'excel')

D = 

    datetime

     01-Jan-2004 00:15:35

When I paste that number into an Excel worksheet and format it as a Date, the result matches D rather than the information you posted.

If you really do need the datetime broken into its constituent parts you can use the datevec function on D, but there may be a way to achieve your ultimate goal without needing to perform this splitting. If you describe how you want to use this data we may be able to offer suggestions for how to achieve your goal without datevec.

[Jing Ci Neo]

Yes indeed I had an excel file and one of the columns was in the format 1/1/2004 12:13:58 AM. I used xlsread to store it as a variable in matlab, and got a vector of those serial date numbers.

My ultimate goal is to feed each of those values (yr, mth, day, hr, min, s) into a function, so I need those values separately as vectors. I tried using datetime, then datevec but I got a weird result. Datetime gives the vector 02-Jan-0104 00:13:58 when it's supposed to be 01-Jan-2004, so datevec turned out wrong.

[Steven Lord]

Which release are you using? If you're using release R2013b or later, consider reading your data in using readtable instead of xlsread. If you do, depending on which release you're using you may be able to read the dates into a table directly from Excel using the 'DatetimeType', 'exceldatenum' name-value pair argument.

[Jing Ci Neo]

I wasn't able to use readtable successfully but managed to manipulate the data in excel. Thanks for your help anyway! I really appreciate it.