Importing data from csv file and my time data is being ruined changes from 24 hour time to 59 hour. How do I fix?

I have a csv file and I'm trying to import times in the format of HH:mm:ss.SSS a, the data is in one column and looks like 1/2/1900 13:13:21.897 AM. How do I extract just the time from this column? I tried to export with format 'mm/dd/yyyy HH:mm:ss.SSS a', but when I do this my data is stored in matlab as 59:13:21.897. The hour time is saved as 59 no matter how I try to read the table. How can I fix this?

All help is appreciated, thank you!

Answer 1

[Stephen23]

"I tried to export with format 'mm/dd/yyyy HH:mm:ss.SSS a', "

Months are MM, minutes are mm:

https://www.mathworks.com/help/matlab/ref/datetime.html#buhzxmk-1-Format

"How can I fix this?"

Use the correct format for months. But you have another problem:

in = '1/2/1900 13:13:21.897 AM';
dt = datetime(in, 'inputFormat','MM/dd/y HH:mm:ss.SSS a')

Warning: The format 'MM/dd/y HH:mm:ss.SSS a' contains fields for 24 hour of day (H) and for day period (a). This will cause unexpected results when converting from text. See the datetime.Format property for a complete description of the identifiers used in datetime formats.

dt = datetime
   02-Jan-1900 00:13:21

As the warning states, it is highly unusual to use 24 hour times with AM/PM, and causes unexpected outputs.

Perhaps you want this:

dt = datetime(regexprep(in,'\s*[AP]M$',''), 'inputFormat','MM/dd/y HH:mm:ss.SSS')

dt = datetime
   02-Jan-1900 13:13:21

"How do I extract just the time from this column?"

Use TIMEOFDAY(): https://www.mathworks.com/help/matlab/ref/datetime.timeofday.html

t = timeofday(dt)

t = duration
   13:13:21

PS: Practice reading the documentation, you can find all date/time functions here:

https://www.mathworks.com/help/matlab/date-and-time-operations.html