How to convert respectively four column to date formatting at MATLAB?

1
votes

Excel file(104976x10) includes large data.

  • A column: Time (unit year)
  • B column: Year
  • C column: Day of the year
  • D column: Hour
  • E column: Minute
  • and others including values

I would like to convert column which begins with B column until E column to date format like 'dd/mm/yyyy HH:MM'.

Example for the data:

1998,41655251   1998    152   1   0   12,5  12,0    11,8    11,9    12,0

I would like to do date instead of 2-th, 3-th, 4-th and 5-th columns.

1998,41655251   01/06/1998 01:00    12,5  12,0  11,8  11,9  12,0

or

1998,41655251   01/06/1998 01:00  1998  152   1   0   12,5  12,0    11,8    11,9    12,0
1
matlabdateconverter

1 Answers

0
votes

Welcome to SO. Matlab has two types of date-format:

  1. datetime, introduced in 2014b.
  2. datenum, introcuced in long ago (before 2006b), it is basically a double precision value giving the number of days from January 0, 0000.

I think the best way is to use datetime, and give it the year, month, day, hour and minute values like this:

t=datetime(1998,0,152,1,0,0)

t= '01-May-1998 01:00:00'

As you can see the days automatically overflow into the months. But I end up 1st of may, not 1st of june like in your example.

to change the format:

t.Format='dd/MM/yyyy hh:mm'

t= '01/05/1998 01:00'

to convert it to a string, you can simply use string(t)

This is an example that combines the above functions to read an xlsx file and writes a new one with the updated column.

data=xlsread('test.xlsx');
S = size(data);
t = datetime(data(:,2),0,data(:,3),data(:,4),data(:,5),0);
t.Format='dd/MM/yyyy HH:mm';
data2=num2cell(data(:,1));
data2(:,2)=cellstr(string(t));
data2(:,3:S(2)-3)=num2cell(data(:,6:end));
xlswrite('test2.xlsx',data2);