Convert time column from hours to ddmmmyyyy HH:MM:SS

My time column is in hours (0000 01JAN1900=0hr) and starts from 999,312h (meaning 0000 01JAN2014). How would I convert a column vector of times (hrs) into a column of time in ddmmmyyyy HH:MM:SS format?

[KSSV]

Read about datetime.

Answer 1

[Stephen23]

dth = 999312 + [0;1;12;24;36;42;31*24] % fake data

dth = 7×1

999312 999313 999324 999336 999348 999354 1000056

●

dtm = datetime(dth*60*60, 'ConvertFrom','epochtime','Epoch','1900-01-01', 'Format','ddMMMyyyy HH:mm:ss')

dtm = 7×1 datetime array
   01Jan2014 00:00:00
   01Jan2014 01:00:00
   01Jan2014 12:00:00
   02Jan2014 00:00:00
   02Jan2014 12:00:00
   02Jan2014 18:00:00
   01Feb2014 00:00:00

[Phoebe Armitage]

Thank you!

That's almost there, however the days don't seem to be resetting every month, do you know how to corredct this?

e.g. For 01FEB2014 it says 32Feb2014

[Steven Lord]

In a datetime Format, DD represents the day of the year using two digits. You want dd, which represents the day of the month using two digits. See the table in the description of the Format property on the documentation page for the datetime function.

[Stephen23]

@Phoebe Armitage: sorry, I used the wrong day format. I fixed my answer, please try it again.

[Phoebe Armitage]

How about this one:

I have an xls file with a column of data in the format dd/mm/yyyy HH:MM - the first few values are:

31/05/2010 14:50

31/05/2010 15:00

31/05/2010 15:00

etc. (increasing every 10mins)

I have imported this into matlab using xlsread at the data in this variable column reads:

40330

40330

40330

...

I have then used the datestr() function which ouputs the times right, but not the date:

'01-Jun-0110 14:50:00'

'01-Jun-0110 15:00:00'

'01-Jun-0110 15:10:00'

'01-Jun-0110 15:20:00'

'01-Jun-0110 15:30:00'

etc.

How do I output the correct dates?

[Stephen23]

"How do I output the correct dates?"

The value stored by Excel for all dates (regardless of their format) counts days since 1900 (or 1904):

https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system

whereas MATLAB counts days from day zero of year zero, as documented here:

https://www.mathworks.com/help/matlab/ref/datenum.html

When you call datestr of course its input must be a MATLAB date number (with an epoch of year 0), not an Excel one (with a different epoch) but you can make the adjustment yourself:

vec = [40330;40330;40330];
adj = datenum(1900,1,1)-2;
datestr(vec+adj)

ans = 3x11 char array
    '01-Jun-2010'
    '01-Jun-2010'
    '01-Jun-2010'

Another option would be to use this function (if you have the Financial Toolbox):

https://www.mathworks.com/help/finance/x2mdate.html

tmp = x2mdate(vec);
datestr(tmp)

ans = 3x11 char array
    '01-Jun-2010'
    '01-Jun-2010'
    '01-Jun-2010'

A better option would be to use datetime:

datetime(vec,'ConvertFrom','excel')

ans = 3×1 datetime array
   01-Jun-2010
   01-Jun-2010
   01-Jun-2010

But most likely you should avoid using xlsread and instead use readtable, which supports reading date/time values directly into datetime objects, and does all the relevant conversions for you.

[Phoebe Armitage]

Ahh! I just used readtable - much simpler thank you!

[Peter Perkins]

Just to be clear: "whereas MATLAB counts days from day zero of year zero" refers to the older way of working with dates/times in MATLAB. The newer better way to do it is, as Stephen says, datetime, which doesn't work like that. datetime has been around since R2014b.

Answer 2

[Steven Lord]

dth = 999312 + [0;1;12;24;36;42] % fake data
start = datetime(1900, 1, 1);
dates = start + hours(dth)

[Stephen23]

+1 very nice