merging date and time

Hello!

I have a table with date (mm/dd/yyyy) in one column in and time(matlab serial number for hh:mm:ss) in the other. I want to create a new column within the same table or outside of it that combines them into one column, into the form mm/dd/yyyy hh:mm:ss. Can this be done?

Thank you!

Answer 1

[Paolo]

Nicole, the following example should help you in what you are trying to do. I have added some comments in the code to help you. To perform arithmetic operations on dates and times, you must ensure that they have the same format as shown below.

It initially creates a table which looks like this:

      dates         times   
    __________    __________

    09/29/2017    7.3706e+05
    09/28/2017    7.3706e+05
    09/27/2017    7.3706e+05

as you indicated that you have dates in the first column and datenums in the second column.

 %First column of table with dates.
 DateStrings = {'29/09/2017','28/09/2017','27/09/2017'};
 dates = datetime(DateStrings,'Format','MM/dd/yyyy')';

 %Second column of table with times in datenum format.
 TimeStrings = {'20:30:30','19:30:21','14:32:01'};
 times = datenum(TimeStrings);

 %This will create the table as shown in the first part of the answer.
 t = table(dates,times);

 %Format both columns to MM/dd/yyyy HH:mm:SS for addition.
 dates = datetime(t.dates,'Format','MM/dd/yyyy HH:mm:SS');
 times = datetime(t.times,'ConvertFrom','datenum','Format','MM/dd/yyyy HH:mm:SS');

 %Add dates to times.
 fullt = dates+timeofday(times);
 t.DatesNTimes = fullt;

The final table contains the third additional column which is the result of the addition of the date of the first and the time of the second.

       dates         times          DatesNTimes    
    __________    __________    ___________________

    09/29/2017    7.3706e+05    09/29/2017 20:30:00
    09/28/2017    7.3706e+05    09/28/2017 19:30:00
    09/27/2017    7.3706e+05    09/27/2017 14:32:00

[Peter Perkins]

Paolo, spot on, but one small tweak:

t.times = days(t.times)

will convert those datenums representing time of day directly to durations (recall that datenums count time in units of 24hr days). Then add them to the "date" datetimes as you've shown.

[Paolo]

Hi Peter, thank you for your comment. I tried the method you suggested but it did not seem to work for me.

For the table in the example,

 t = table(dates,times);
 dates = datetime(t.dates,'Format','MM/dd/yyyy HH:mm:SS');
 times = days(t.times);

If I use timeofday

 fullt = dates+timeofday(times);

I get the following error:

Undefined function 'timeofday' for input arguments of type 'duration'.

Am I perhaps missing a step? Thanks.

[Walter Roberson]

fullt = dates + times;

[Paolo]

The result of that operation is:

3×1 datetime array

   10/01/4035 20:30:00
   09/30/4035 19:30:00
   09/29/4035 14:32:00

[Louis Keiner]

Thank you! This has helped a lot.

[Peter Perkins]

Paola, timeofday is a function that accepts a datetime and returns a duration. There's no reason to call it on a duration.

[Sreeraj T]

In 3rd line of the code, we have:

dates = datetime(DateStrings,'Format','MM/dd/yyyy')';

The we also have:

dates = datetime(t.dates,'Format','MM/dd/yyyy HH:mm:SS');

Can't we put the correct format in third line itself? It will just add 00:00:00 to the exisiting date variable.

[Stephen23]

@Sreeraj T: the 3rd line would be much more robust if it used INPUTFORMAT. Note that all of the lines up to and including t = table(...) are just creating fake data for illustrating the approach given in the answer, which you do not need to replicate. The OP wrote that "I have a table with date (mm/dd/yyyy) in one column..." which is why the fake data uses that format**: because that is what the OP wrote they had. Your suggestion would change the fake data to no longer represent what the OP had.

Converting from serial date number to datetime and then to duration involves superfluous type conversions. It is also not required to "Format both columns to MM/dd/yyyy HH:mm:SS for addition", so there is anyway some obvious potential for improvement here.

Unfortunately the OP did not tell us what input formats their data has, otherwise I would make some suggestions.

** almost, I suspect that the swapped day/month was just an oversight.

[Sreeraj T]

Ok.

I also have similar problem: one coulm with date with 'dd-MM-UUUU' format and another column with time in format 'HH:mm:ss.SSS'. So to combine I used the command:

Dates=datetime(date_var,"Format","dd-MM-uuuu HH:mm:ss.SSS") % this will append 00:00:00.000 to the date
Times=datetim(datenum(time_var),"ConvertFrom","datenum","Format","dd-MM-uuuu HH:mm:ss.SSS")
DateTimeVar=Dates+timeofday(Times)

This is giving the required values.

[Stephen23]

@Sreeraj T: The conversion to DATENUM is superfluous. The conversion of serial date numbers to DATETIME is most likely superfluous. There is no point in setting the FORMAT of TIMES.

date_var = {'29-09-2025';'28-09-2025';'27-09-2025'};
time_var = {'13:55:06';'13:55:07';'13:55:08'};
Dates = datetime(date_var,"InputFormat","dd-MM-uuuu","Format","dd-MM-uuuu HH:mm:ss.SSS");
Times = duration(time_var);
DateTimeVar = Dates+Times

DateTimeVar = 3×1 datetime array
   29-09-2025 13:55:06.000
   28-09-2025 13:55:07.000
   27-09-2025 13:55:08.000

Selecting the best approach depends on your input data, which you have not provided or described.

[Sreeraj T]

@Stephen23, Thank you for the clarification and sorry for the delay.

Just to clarify: yes, I haven't given the data explicitly, but I have given it's structure.

Thanks again.