Convert MMddyyyy from excel, add separate HH:mm from excel into datetime table

Currently I have an excel file that has date in one cell in the format "MMddyyyy" and in a separate cell with a time in the format "HH:mm". I am trying to combine them into a single datetime entry with format "MM/dd/yyyy HH:mm" I can use within appdesigner to fill text fields, and eventually to parse a different file and clean it up to include only specified dates and times with associated data.

This is my code;

datetime.setDefaultFormats('default','MM/dd/yyyy HH:mm');
%load the trial start date and convert to a datetime usable
%format
dateentry = string(readmatrix(strcat(app.directory,'\',app.TrialDataFilename),'Sheet','Data','Range','B2:B2'));
startdate = datetime(dateentry,"InputFormat","MMddyyyy");
timeentry = readmatrix(strcat(app.directory,'\',app.TrialDataFilename),'Sheet','Data','Range','A21:A21');
starttime = datetime(timeentry,"ConvertFrom","datenum");
finalentry = startdate+timeofday(starttime);

%Add to the start time field
app.StartTimemmddyyyyHHMMEditField.Value = string(finalentry);

This just seems totally unreasonable. It does work, but there has to be a more elegant way to do this without creating a ton of variables just for this conversion. I'll have to do this for quite a few date and time entries from an operator log excel sheet and I am concerned about bogging down the app to a crawl.

Answer 1

[Steven Lord]

Currently I have an excel file that has date in one cell in the format "MMddyyyy" and in a separate cell with a time in the format "HH:mm".

So something like this?

sampleData = {'01242024', '14:09'}

sampleData = 1×2 cell array
    {'01242024'}    {'14:09'}

How would I convert this into a datetime with date and time data? Call datetime and duration and add them together. The second cell in sampleData doesn't have date information associated with it, so converting it to a datetime doesn't seem appropriate. It's an amount of time, which is what a duration array is.

d = datetime(sampleData{1}, InputFormat = 'MMddyyyy') + ...
    duration(sampleData{2}, InputFormat = 'hh:mm')

d = datetime
   24-Jan-2024 14:09:00

To use your specified output format:

d.Format = "MM/dd/yyyy HH:mm"

d = datetime
   01/24/2024 14:09

To turn it into string data:

s = string(d)

s = "01/24/2024 14:09"

Or to combine this into one command:

s2 = string(...
    datetime(sampleData{1}, InputFormat = 'MMddyyyy', Format="MM/dd/yyyy HH:mm") + ...
    duration(sampleData{2}, InputFormat = 'hh:mm'))

s2 = "01/24/2024 14:09"

Note that just because I used a cell array in the example above where each cell contained only one piece of data, that doesn't mean you couldn't convert multiple dates and times at once.

sampleData = {["12252023", "07041776"; "01242024", "03141529"], ...
              ["00:00",    "12:34"   ; "14:09",    "08:45"]};
s3 = string(...
    datetime(sampleData{1}, InputFormat = 'MMddyyyy', Format="MM/dd/yyyy HH:mm") + ...
    duration(sampleData{2}, InputFormat = 'hh:mm'))

s3 = 2×2 string array
    "12/25/2023 00:00"    "07/04/1776 12:34"
    "01/24/2024 14:09"    "03/14/1529 08:45"

Or just use string arrays directly, rather than packing them in a cell.

str1 = sampleData{1}

str1 = 2×2 string array
    "12252023"    "07041776"
    "01242024"    "03141529"

str2 = sampleData{2}

str2 = 2×2 string array
    "00:00"    "12:34"
    "14:09"    "08:45"

s4 = string(...
    datetime(str1, InputFormat = 'MMddyyyy', Format="MM/dd/yyyy HH:mm") + ...
    duration(str2, InputFormat = 'hh:mm'))

s4 = 2×2 string array
    "12/25/2023 00:00"    "07/04/1776 12:34"
    "01/24/2024 14:09"    "03/14/1529 08:45"

[Robert Rogers]

This is essentially what I was looking for, something more elegant than the blocky nature of my solution. I didn't realize you could specify format like that within the datetime function input arguments, so thanks for that!