How to extract date time from format unique to InputFormat?

I have data set extracted in the form ´05/17/24 04p:48:15.8148625´. These data sets are not fond in the InputFormat list, contains a 'p' for postmeridian, and has seconds up to 7 decimal places. I am ok if it is inevitable to round-off the seconds, however, I will be working with several formats and the manual way of extracting parts of strings, joining them together in InputFormat list is quite hectic and it slows down my code. Is there a way we can simply use datetime command to convert the text into datetime? I will appreciate your help. Thanks.

load('data_imp.mat');
dttime = datetime(data_imp,"InputFormat","MM/dd/uu 'T'HH:mm:ss.SSSSSSS");

Error using datetime (line 257)
Unable to convert the text to datetime using the format 'MM/dd/uu 'T'HH:mm:ss.SSSSSSS'.

Answer 1

[Shashi Kiran]

Hi @Muha,

To convert your data into "datetime" format in MATLAB, you can use "regexprep" to handle the PM adjustment. Here’s how:

load('data_imp.mat')
% Adjust PM hours
data_adj = regexprep(string(data_imp), '(\d{2})p:', '${num2str(str2double($1) + 12, ''%02d'')}:');  
% Convert to datetime
dttime = datetime(data_adj, 'InputFormat', 'MM/dd/yy HH:mm:ss.SSSSSSS')

dttime = 23x1 datetime array
   17-May-2024 16:48:15
   17-May-2024 16:48:30
   17-May-2024 16:48:45
   17-May-2024 16:48:48
   17-May-2024 16:48:49
   17-May-2024 16:48:49
   17-May-2024 16:48:50
   17-May-2024 16:48:53
   17-May-2024 16:48:53
   17-May-2024 16:48:54
   17-May-2024 16:48:54
   17-May-2024 16:48:54
   17-May-2024 16:48:55
   17-May-2024 16:48:55
   17-May-2024 16:48:57
   17-May-2024 16:48:57
   17-May-2024 16:48:57
   17-May-2024 16:48:58
   17-May-2024 16:48:58
   17-May-2024 16:48:58
   17-May-2024 16:48:59
   17-May-2024 16:49:00
   17-May-2024 16:49:01

For more details on "regexprep", refer to the documentation here: https://www.mathworks.com/help/matlab/ref/regexprep.html

Hope this helps!

[Muha]

Thank you. This is what I was looking for. Thank you for your help. ~Best

Answer 2

[Cris LaPierre]

The format used to represent am/pm (04p:48:15.8148625) is not one recognized by MATLAB. You therefore need to manipulate your data into a format that MATLAB can recognize.

My approach would be

• capture which rows contain pm times
• remove the am/pm designation
• convert times to datetimes
• add 12 hours to rows containing pm times

load('data_imp.mat')
data_imp

data_imp = 23x1 cell array
    {'   05/17/24 04p:48:15.8148625'}
    {'   05/17/24 04p:48:30.7939140'}
    {'   05/17/24 04p:48:45.6719270'}
    {'   05/17/24 04p:48:48.5268930'}
    {'   05/17/24 04p:48:49.3434208'}
    {'   05/17/24 04p:48:49.8132723'}
    {'   05/17/24 04p:48:50.8816138'}
    {'   05/17/24 04p:48:53.4666198'}
    {'   05/17/24 04p:48:53.7914052'}
    {'   05/17/24 04p:48:54.0146895'}
    {'   05/17/24 04p:48:54.0894605'}
    {'   05/17/24 04p:48:54.5264970'}
    {'   05/17/24 04p:48:55.0232162'}
    {'   05/17/24 04p:48:55.4721045'}
    {'   05/17/24 04p:48:57.5493493'}
    {'   05/17/24 04p:48:57.6698808'}
    {'   05/17/24 04p:48:57.9283307'}
    {'   05/17/24 04p:48:58.2437670'}
    {'   05/17/24 04p:48:58.2820553'}
    {'   05/17/24 04p:48:58.5471500'}
    {'   05/17/24 04p:48:59.6692505'}
    {'   05/17/24 04p:49:00.7394038'}
    {'   05/17/24 04p:49:01.3870950'}

% capture pm times
pmTms = cellfun(@(x) contains(x,'p:'),data_imp);
% remove am/pm designation
data_imp = replace(data_imp,lettersPattern + ':',':')

data_imp = 23x1 cell array
    {'   05/17/24 04:48:15.8148625'}
    {'   05/17/24 04:48:30.7939140'}
    {'   05/17/24 04:48:45.6719270'}
    {'   05/17/24 04:48:48.5268930'}
    {'   05/17/24 04:48:49.3434208'}
    {'   05/17/24 04:48:49.8132723'}
    {'   05/17/24 04:48:50.8816138'}
    {'   05/17/24 04:48:53.4666198'}
    {'   05/17/24 04:48:53.7914052'}
    {'   05/17/24 04:48:54.0146895'}
    {'   05/17/24 04:48:54.0894605'}
    {'   05/17/24 04:48:54.5264970'}
    {'   05/17/24 04:48:55.0232162'}
    {'   05/17/24 04:48:55.4721045'}
    {'   05/17/24 04:48:57.5493493'}
    {'   05/17/24 04:48:57.6698808'}
    {'   05/17/24 04:48:57.9283307'}
    {'   05/17/24 04:48:58.2437670'}
    {'   05/17/24 04:48:58.2820553'}
    {'   05/17/24 04:48:58.5471500'}
    {'   05/17/24 04:48:59.6692505'}
    {'   05/17/24 04:49:00.7394038'}
    {'   05/17/24 04:49:01.3870950'}

% convert to datetime
dttime = datetime(data_imp,"InputFormat","MM/dd/yy hh:mm:ss.SSSSSSS");
% add 12 hours to pm times
dttime(pmTms) = dttime + hours(12)

dttime = 23x1 datetime array
   17-May-2024 16:48:15
   17-May-2024 16:48:30
   17-May-2024 16:48:45
   17-May-2024 16:48:48
   17-May-2024 16:48:49
   17-May-2024 16:48:49
   17-May-2024 16:48:50
   17-May-2024 16:48:53
   17-May-2024 16:48:53
   17-May-2024 16:48:54
   17-May-2024 16:48:54
   17-May-2024 16:48:54
   17-May-2024 16:48:55
   17-May-2024 16:48:55
   17-May-2024 16:48:57
   17-May-2024 16:48:57
   17-May-2024 16:48:57
   17-May-2024 16:48:58
   17-May-2024 16:48:58
   17-May-2024 16:48:58
   17-May-2024 16:48:59
   17-May-2024 16:49:00
   17-May-2024 16:49:01

[Muha]

Thank you for taking your time. I appreciate it. regexprep worked more efficiently. But I appreciate your time. Thanks.

Answer 3

[Peter Perkins]

Another possibility:

str = "05/17/24 04p:48:15.8148625"

str = "05/17/24 04p:48:15.8148625"

str = replace(str,"p","pm")

str = "05/17/24 04pm:48:15.8148625"

datetime(str,InputFormat="MM/dd/yy hha:mm:ss.SSSSSSS")

ans = datetime
   17-May-2024 16:48:15

[Cris LaPierre]

That's pretty slick. Didn't think to try that.