Reformat and Merge Date and Time columns

Hello. Could use some help merging the Date and Time columns in the attached data file. Date (Column 1) is a DateTime vector dd-MMM-yyyy and Time is a vector that contains 31-Dec-1899 HH:mm:ss What I need is to delete the 31-Dec-1899 default year and merge the true Date (02-Sep-2016) with the remaining Time from column 2. I have tried the following to no avail.

load RVdata.mat
tim = datetime(RVdata.TimeHHmmss, 'InputFormat','dd-MMM-yyyy HH:mm:ss', 'Format','HH:mm:ss')

[Stephen23]

Use the inbuilt TIMEOFDAY function, just as Star Strider showed:

https://www.mathworks.com/matlabcentral/answers/2019466-reformat-and-merge-date-and-time-columns#answer_1308261

Answer 1

[Star Strider]

For what it’s worth —

LD = load('RVdata.mat');
data = LD.data

data = 79×6 table
       Date             TimeHHmmss             ObservedSource        LAeqmaxdB    SourceClass    SourceCat
    ___________    ____________________    ______________________    _________    ___________    _________

    02-Sep-2016    31-Dec-1899 07:54:00    "Budget Truck"              67.3        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:00:30    "Trailen Truck"             69.5        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:08:50    "Trailen Truck"             67.6        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:12:50    "Trailen Truck"             69.5        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:14:20    "W.A. Muson Truck"          65.1        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:17:02    "Ryclear Truck"             67.1        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:18:00    "Shuttle Van"               65.2        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:18:40    "Semi Tractor"              74.2        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:19:00    "Jeeep (Music)"             67.2        other             0    
    02-Sep-2016    31-Dec-1899 08:21:01    "Box Truck"                 65.1        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:22:50    "Siren (Not Observed)"      70.2        other             0    
    02-Sep-2016    31-Dec-1899 08:23:15    "Siren not olosen"          67.2        other             0    
    02-Sep-2016    31-Dec-1899 08:29:00    "The Ride"                  61.6        truck-bus         1    
    02-Sep-2016    31-Dec-1899 08:29:40    "SUV"                       70.2        other             0    
    02-Sep-2016    31-Dec-1899 08:30:47    "Motorcycle (Slow)"         62.1        other             0    
    02-Sep-2016    31-Dec-1899 08:31:30    "Lays Potato Truck"         62.6        truck-bus         1    

data.Date = data.Date + timeofday(data.TimeHHmmss);
data = removevars(data,2)

data = 79×5 table
            Date                ObservedSource        LAeqmaxdB    SourceClass    SourceCat
    ____________________    ______________________    _________    ___________    _________

    02-Sep-2016 07:54:00    "Budget Truck"              67.3        truck-bus         1    
    02-Sep-2016 08:00:30    "Trailen Truck"             69.5        truck-bus         1    
    02-Sep-2016 08:08:50    "Trailen Truck"             67.6        truck-bus         1    
    02-Sep-2016 08:12:50    "Trailen Truck"             69.5        truck-bus         1    
    02-Sep-2016 08:14:20    "W.A. Muson Truck"          65.1        truck-bus         1    
    02-Sep-2016 08:17:02    "Ryclear Truck"             67.1        truck-bus         1    
    02-Sep-2016 08:18:00    "Shuttle Van"               65.2        truck-bus         1    
    02-Sep-2016 08:18:40    "Semi Tractor"              74.2        truck-bus         1    
    02-Sep-2016 08:19:00    "Jeeep (Music)"             67.2        other             0    
    02-Sep-2016 08:21:01    "Box Truck"                 65.1        truck-bus         1    
    02-Sep-2016 08:22:50    "Siren (Not Observed)"      70.2        other             0    
    02-Sep-2016 08:23:15    "Siren not olosen"          67.2        other             0    
    02-Sep-2016 08:29:00    "The Ride"                  61.6        truck-bus         1    
    02-Sep-2016 08:29:40    "SUV"                       70.2        other             0    
    02-Sep-2016 08:30:47    "Motorcycle (Slow)"         62.1        other             0    
    02-Sep-2016 08:31:30    "Lays Potato Truck"         62.6        truck-bus         1    

.

[William Rose]

@Star Strider, Nice! It is a more elegant solution.

[Douglas Leaffer]

Thank you both !

[Star Strider]

My pleasure!

It make optimal use of the datetime functions.

Answer 2

[William Rose]

@Douglas Leaffer,

load('RVdata');
dt1=data.(1)+(3600*hour(data.(2))+60*minute(data.(2))+second(data.(2)))/86400;
dt1.Format='yyyy-MMM-dd hh:mm:ss';
dt1(1:4)

ans = 4×1 datetime array
   2016-Sep-02 07:54:00
   2016-Sep-02 08:00:30
   2016-Sep-02 08:08:49
   2016-Sep-02 08:12:50

Try it. Good luck!

[Douglas Leaffer]

Thank you ! Works fine for my needs.