readtable with datetime, format problem

I'm reading in an excel file that contains the date/time stamp of a recording in one column. The format of the date/time in the original file is day/month/year hour:min:sec PM. The date is sometimes correctly read, but in instances when the day/month is arbitrary (day is <=12), the month and day is swapped

Example:

Recording was made on December 6, 2022 (saved in excel sheet as 6/12/2022 7:56:39 PM)

I run these lines of code:

%%
opts = detectImportOptions(filepath,'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time",'InputFormat','d/MM/uuuu h:mm:ss aa','DatetimeLocale','de_BE','DatetimeFormat','d/MM/uuuu h:mm:ss aa');
nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');
%%

nrtPeaks.RecordingDate_Time(1) = 12-Jun-2022 19:56:39

How do I fix this?

The code runs correctly in some instances but not others. I played around with readcell and had the same issue.

[Rachel Scheperle]

PS. I also have played around with setvartype so that the data are read as characters rather than a datetime.

%%
opts = detectImportOptions(filepath,'Sheet','NRT Peaks');
opts = setvartype(opts,"RecordingDate_Time",'char');
nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');
%%

My output is a datetime array even though I specified that I wanted a character type of variable:

nrtPeaks.RecordingDate_Time(1) = 12/06/2022 07:56:30 PM

[Stephen23]

"I'm reading in an excel file that contains the date/time stamp of a recording in one column. The format of the date/time in the original file is day/month/year hour:min:sec PM"

Note that Excel (using the contraversial Office Open XML standard, i.e. .XLSX) does not store dates with a format. All dates are stored as serial date numbers (by default the 1st January 1900 is serial number 1). And MATLAB has no problem importing Excel's serial date numbers.

So it really depends what you have in your "Excel Sheet":

• is the cell content really formatted as a date?
• is it really an Excel file or is it really e.g. a .CSV file (which exist quite independently of Excel)?
• ... something else?

We don't know, because you did not upload a sample data file by clicking the paperclip button.

[Rachel Scheperle]

Thank you.

I've attached sample data.

The cell content is probably not formatted as a date (it says "custom") when I click on it. The excel file is created when I export the data from another software program, and I don't have control over how the other program is formating the saved data.

[Stephen23]

@Rachel Scheperle: thanks for uploading the file. I took a look, and those dates are correctly stored as serial date numbers. For example, here is the first date in cell C2 (line 66):

That is the number of days since 1 Jan 1900, which is very easy to calculate: it is the 12th of June 2022. You can easily check that yourself using Excel, MATLAB, or some online tools.

"...but unfortunately the correct date is supposed to be December 6, 2022."

Then the person or application that created that file really messed up big time. That would mean that all of the dates are incorrectly encoded (most likely because they did not know how to specify MS Excel dates, nor did they check their work). Basically they encoded dates using units which just happen to look like the units that they wanted to have, but were actually not those units. Learn from their mistake! (they obviously didn't)

To fix their mistake you would have to un-swap those unit values somehow.

[Stephen23]

"If you mean that within a single file some dates are imported correctly"

Actually MATLAB imports the dates 100% correctly.

The problem is (apparently) that the person/application that created that file encoded the dates incorrectly.

Note that the custom format code within Excel is "DD/MM/YYYY HH:MM", which exactly corresponds to those dates being interpreted as 12th of June 2022. So either the dates have been wrongly defined... or the OP is wrong about what they represent. Either is quite possible.

"if you mean that for some files the dates are imported correctly and for other files the dates are not imported correctly"

There is absolutely no way to distinguish from the serial date numbers themselves which ones might have been created incorrectly, and which ones correctly. They are all just numbers.

In such a situation we would have to rely on meta-information.

[Voss]

Yes, I agree. As I said when I said, "Are you sure that the error is not with the software that exports this data?"

[Rachel Scheperle]

Thank you for these replies. I'm a novice programmer, so my assumption is that the issue is an error I've made, but I know for certain that the date of the recording was December 6 of 2022, so the fact that the serial number is June is quite shocking.

I uploaded another file as an example of one that was importing "correctly" with my original code, which was:

nrtPeaks = readtable(filepath,'Sheet','NRT Peaks');

(I originally did not specify any opts until noticing that some file dates were being read "incorrectly" and I started to try to fix it).

In exploring the issue further, I have noticed that when the default options are used for readtable, the nrtPeaks.RecordingDate_Time is read as a character array in the excel file below (-6m-), but as a datetime type for the first file I uploaded (-2w-). Any thoughts to why that happens?

When the column is read as a character array, my following lines of code (using a datenum to datetime conversion and specifying the format) works to provide the correct date.

Since this seemed to be a potential solution (that doesn't require fixing the original data export, which is out of my control), I tried to force readtable to consider "RecordingDate_Time" as a character using

opts = setvartype(opts,"RecordingDate_Time",'char');

nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');

%%

My output for the first file (-2w-) is a datetime array even though I specified that I wanted a character type of variable:

nrtPeaks.RecordingDate_Time(1) = 12/06/2022 07:56:30 PM

%%

Any thoughts on this?

The data are collected longitudinally, so the date is an important variable.

I will probe into the data export issue further (but I'm not extremely hopeful). In the meantime, I have a pile of data and an analysis deadline. I'm trying to think creatively of a work around that isn't going to require manually entering or checking each date. If there is a flaw in my logic re the forcing the variable to be read as a character, is there something similar that might be a work around?

Thanks for all of your input. I appreciate your problem-solving skills.

[Stephen23]

"Any thoughts on this?"

If it works, do it.

Note that by importing as text and then using datetime you are essentially un-swapping the units... thus fixing the problem.

"I uploaded another file as an example of one that was importing "correctly" with my original code"

The "dates" in that file are not actually Excel serial date numbers at all, they are simply text. But interestingly they are stored as shared strings within the workbook (rather than individually for each cell):

It is pretty clear that whatever/whoever created those files... does not understand Excel at all.

It also makes it clear, that your approach of importing all of the dates as text is a good workaround/approach to take.

[Cris LaPierre]

Can you change the format of one of your dates to General and confirm that the Excel datenum starts with 44901?

[Cris LaPierre]

About the 6m file, I wanted to confirm that the dates got stored as text, likely because the first number can't be a month. If I change the day to be something less than 13, Excel immediately formats it as a date using month/day/year.

As for reading in as text, I see that when the date is an Excel datenum, the month gets formatted to MMM automatically. This is probably to avoid confusion.

opts = detectImportOptions("P50-DE-test-2w...-IPG7-e3.xlsx",'Sheet','NRT Peaks');
opts = setvartype(opts,"RecordingDate_Time",'char');
T1 = readtable("P50-DE-test-2w...-IPG7-e3.xlsx",opts)

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T1 = 20×11 table
      Status      NRTNr       RecordingDate_Time       NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    __________    _____    ________________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    {0×0 char}      1      {'12-Jun-2022 19:56:39'}         325.4             -147.54             813.4             -23.224        {'ECAP'}               {'Off'}                       {'Off'}                   124.31      
    {0×0 char}      2      {'12-Jun-2022 19:56:54'}         374.2              -147.2             715.8             -38.251        {'ECAP'}               {'Off'}                       {'Off'}                   108.94      
    {0×0 char}      3      {'12-Jun-2022 19:57:10'}         374.2             -148.56             715.8             -37.226        {'ECAP'}               {'Off'}                       {'Off'}                   111.33      
    {0×0 char}      4      {'12-Jun-2022 19:57:25'}         374.2              -143.1             715.8             -39.275        {'ECAP'}               {'Off'}                       {'Off'}                   103.82      
    {0×0 char}      5      {'12-Jun-2022 19:57:40'}         374.2             -134.56             764.6             -36.885        {'ECAP'}               {'Off'}                       {'Off'}                    97.67      
    {0×0 char}      6      {'12-Jun-2022 19:58:02'}         325.4             -132.17             813.4             -35.177        {'ECAP'}               {'Off'}                       {'Off'}                    96.99      
    {0×0 char}      7      {'12-Jun-2022 19:51:49'}         374.2             -106.56               667             -39.617        {'ECAP'}               {'Off'}                       {'Off'}                    66.94      
    {0×0 char}      8      {'12-Jun-2022 19:51:33'}         374.2             -90.163             715.8             -34.494        {'ECAP'}               {'Off'}                       {'Off'}                    55.66      
    {0×0 char}      9      {'12-Jun-2022 19:51:18'}         374.2             -85.382             715.8             -30.054        {'ECAP'}               {'Off'}                       {'Off'}                    55.32      
    {0×0 char}     10      {'12-Jun-2022 19:51:03'}         374.2             -78.551               667              -31.42        {'ECAP'}               {'Off'}                       {'Off'}                    47.13      
    {0×0 char}     11      {'12-Jun-2022 19:50:48'}         374.2             -70.696             715.8             -31.079        {'ECAP'}               {'Off'}                       {'Off'}                    39.61      
    {0×0 char}     12      {'12-Jun-2022 19:52:04'}         374.2             -29.371               667             -15.368        {'ECAP'}               {'Off'}                       {'Off'}                       14      
    {0×0 char}     13      {'12-Jun-2022 19:53:33'}         374.2             -21.516             715.8              -8.196        {'ECAP'}               {'Off'}                       {'Off'}                    13.31      
    {0×0 char}     14      {'12-Jun-2022 19:55:47'}         374.2             -21.174             618.2             -14.002        {'ECAP'}               {'Off'}                       {'Off'}                     7.17      
    {0×0 char}     15      {'12-Jun-2022 19:53:48'}         325.4             -17.418             715.8              -6.147        {'ECAP'}               {'Off'}                       {'Off'}                    11.27      
    {0×0 char}     16      {'12-Jun-2022 19:54:41'}         325.4             -16.734             618.2              -8.538        {'ECAP'}               {'Off'}                       {'Off'}                     8.19      

T2 = readtable("P50-DE-test-2w...-IPG7-e3.xlsx",'Sheet','NRT Peaks','DatetimeType','text','TextType','string')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T2 = 20×11 table
    Status    NRTNr      RecordingDate_Time      NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    ______    _____    ______________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

     NaN        1      "12-Jun-2022 19:56:39"         325.4             -147.54             813.4             -23.224         "ECAP"                 "Off"                         "Off"                    124.31      
     NaN        2      "12-Jun-2022 19:56:54"         374.2              -147.2             715.8             -38.251         "ECAP"                 "Off"                         "Off"                    108.94      
     NaN        3      "12-Jun-2022 19:57:10"         374.2             -148.56             715.8             -37.226         "ECAP"                 "Off"                         "Off"                    111.33      
     NaN        4      "12-Jun-2022 19:57:25"         374.2              -143.1             715.8             -39.275         "ECAP"                 "Off"                         "Off"                    103.82      
     NaN        5      "12-Jun-2022 19:57:40"         374.2             -134.56             764.6             -36.885         "ECAP"                 "Off"                         "Off"                     97.67      
     NaN        6      "12-Jun-2022 19:58:02"         325.4             -132.17             813.4             -35.177         "ECAP"                 "Off"                         "Off"                     96.99      
     NaN        7      "12-Jun-2022 19:51:49"         374.2             -106.56               667             -39.617         "ECAP"                 "Off"                         "Off"                     66.94      
     NaN        8      "12-Jun-2022 19:51:33"         374.2             -90.163             715.8             -34.494         "ECAP"                 "Off"                         "Off"                     55.66      
     NaN        9      "12-Jun-2022 19:51:18"         374.2             -85.382             715.8             -30.054         "ECAP"                 "Off"                         "Off"                     55.32      
     NaN       10      "12-Jun-2022 19:51:03"         374.2             -78.551               667              -31.42         "ECAP"                 "Off"                         "Off"                     47.13      
     NaN       11      "12-Jun-2022 19:50:48"         374.2             -70.696             715.8             -31.079         "ECAP"                 "Off"                         "Off"                     39.61      
     NaN       12      "12-Jun-2022 19:52:04"         374.2             -29.371               667             -15.368         "ECAP"                 "Off"                         "Off"                        14      
     NaN       13      "12-Jun-2022 19:53:33"         374.2             -21.516             715.8              -8.196         "ECAP"                 "Off"                         "Off"                     13.31      
     NaN       14      "12-Jun-2022 19:55:47"         374.2             -21.174             618.2             -14.002         "ECAP"                 "Off"                         "Off"                      7.17      
     NaN       15      "12-Jun-2022 19:53:48"         325.4             -17.418             715.8              -6.147         "ECAP"                 "Off"                         "Off"                     11.27      
     NaN       16      "12-Jun-2022 19:54:41"         325.4             -16.734             618.2              -8.538         "ECAP"                 "Off"                         "Off"                      8.19      

T3 = readtable("P50-DE-test-6m...-IPG7-e3.xlsx",'Sheet','NRT Peaks','DatetimeType','text','TextType','string')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T3 = 28×11 table
    Status    NRTNr     RecordingDate_Time     NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    ______    _____    ____________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

     NaN        1      "23/5/2023 19:32:3"          394.2             -79.234             833.4              39.275         "ECAP"                 "Off"                         "Off"                     118.5      
     NaN        2      "23/5/2023 19:39:1"          394.2             -64.207             882.2              53.278         "ECAP"                 "Off"                         "Off"                    117.48      
     NaN        3      "23/5/2023 19:32:19"         394.2             -72.745             882.2              44.057         "ECAP"                 "Off"                         "Off"                     116.8      
     NaN        4      "23/5/2023 19:32:34"         394.2             -80.942               931              34.153         "ECAP"                 "Off"                         "Off"                    115.09      
     NaN        5      "23/5/2023 19:32:49"         345.4             -81.967             833.4              33.811         "ECAP"                 "Off"                         "Off"                    115.77      
     NaN        6      "23/5/2023 19:33:4"          345.4              -78.21             882.2              43.374         "ECAP"                 "Off"                         "Off"                    121.58      
     NaN        7      "23/5/2023 19:39:51"         345.4             -77.868               931              31.762         "ECAP"                 "Off"                         "Off"                    109.62      
     NaN        8      "23/5/2023 19:38:30"         345.4              -78.21               931               29.03         "ECAP"                 "Off"                         "Off"                    107.23      
     NaN        9      "23/5/2023 20:10:37"         345.4             -96.311             882.2              18.101         "ECAP"                 "Off"                         "Off"                    114.41      
     NaN       10      "23/5/2023 19:35:42"         345.4             -97.677             833.4              18.784         "ECAP"                 "Off"                         "Off"                    116.46      
     NaN       11      "23/5/2023 19:39:24"         345.4             -96.311             882.2              18.784         "ECAP"                 "Off"                         "Off"                    115.09      
     NaN       12      "23/5/2023 20:10:52"         345.4             -97.677             833.4               4.781         "ECAP"                 "Off"                         "Off"                    102.45      
     NaN       13      "23/5/2023 19:33:20"         345.4             -96.653             833.4               1.707         "ECAP"                 "Off"                         "Off"                     98.36      
     NaN       14      "23/5/2023 20:11:7"          345.4             -88.456             735.8              -3.756         "ECAP"                 "Off"                         "Off"                     84.69      
     NaN       15      "23/5/2023 19:35:1"          345.4             -82.991             784.6              -5.464         "ECAP"                 "Off"                         "Off"                     77.52      
     NaN       16      "23/5/2023 19:33:35"         345.4             -56.352             735.8              -8.538         "ECAP"                 "Off"                         "Off"                     47.81      

[Rachel Scheperle]

In the -2w- file (which was collected on December 6, 2022), the date column is 44724.8310069444 when converted to "general".

In the -6m- file (which was collected on May 23, 2023), the date column is already showing up as "general" (I'm assuming since it is a string).

I can see that this doesn't solve the problem of "fixing" the incorrectly stored date of the -2w- file. Bummer.

[Stephen23]

"Can you change the format of one of your dates to General and confirm that the Excel datenum starts with 44901?"

Why would it? I already showed the value of what is stored inside the XLSX file. And I explained why.

Here it is again, formatting that date column as number (to show the serial date number):

So I have shown with two different methods what serial date number is stored in the XLSX file and they exactly agree with each other and what the OP showed above. Why do you expect some other value totally unrelated to that which the file actually contains?

[Rachel Scheperle]

Thanks for all of your work on this. I'm convinced at this point that the problem is how the excel sheet is being written when the raw data are exported. I know that the file in question was recorded on Dec 6, 2022, so the excel file was written incorrectly, which is a problem upstream of me. Thanks for helping identify/confirm the root of the problem.

[Cris LaPierre]

@Stephen23, I was trying to convince myself it wasn't due to different regional settings (see here). However, I've since convinced myself that, once it gets converted to a datenum, it doesn't change, even if opened on a computer with different regional settings. Just had to think through it.

Answer 1

[Cris LaPierre]

I think now that the issue is clearer, I'll propose another solution.

The challenge now is that some of your files save the RecordingDate_Time as text (if the day is >12), while others store it as a datenum (if the day is <=12). That means there isn't a single import option you can use that will work with all your files.

Since you are in a time crunch to start your analysis, I'd propose a new approach. Load your table letting MATLAB decide how to read in that variable, and then convert that column to the correct datetime based on the datatype. Here, I wrote a function called formatTbl for that purpose.

I have used a month format so that there is no confusion between day and month. Feel free to update it. This is not extensively tested. I leave it to you to validate the results.

opts = detectImportOptions("P50-DE-test-2w...-IPG7-e3.xlsx","Sheet","NRT Peaks","TextType","string");
T1 = readtable("P50-DE-test-2w...-IPG7-e3.xlsx",opts);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T1 = formatTbl(T1)

T1 = 20×11 table
     Status      NRTNr     RecordingDate_Time      NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    _________    _____    _____________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    <missing>      1      6/Dec/2022 7:56:39 PM         325.4             -147.54             813.4             -23.224         "ECAP"                 "Off"                         "Off"                    124.31      
    <missing>      2      6/Dec/2022 7:56:54 PM         374.2              -147.2             715.8             -38.251         "ECAP"                 "Off"                         "Off"                    108.94      
    <missing>      3      6/Dec/2022 7:57:10 PM         374.2             -148.56             715.8             -37.226         "ECAP"                 "Off"                         "Off"                    111.33      
    <missing>      4      6/Dec/2022 7:57:25 PM         374.2              -143.1             715.8             -39.275         "ECAP"                 "Off"                         "Off"                    103.82      
    <missing>      5      6/Dec/2022 7:57:40 PM         374.2             -134.56             764.6             -36.885         "ECAP"                 "Off"                         "Off"                     97.67      
    <missing>      6      6/Dec/2022 7:58:02 PM         325.4             -132.17             813.4             -35.177         "ECAP"                 "Off"                         "Off"                     96.99      
    <missing>      7      6/Dec/2022 7:51:49 PM         374.2             -106.56               667             -39.617         "ECAP"                 "Off"                         "Off"                     66.94      
    <missing>      8      6/Dec/2022 7:51:33 PM         374.2             -90.163             715.8             -34.494         "ECAP"                 "Off"                         "Off"                     55.66      
    <missing>      9      6/Dec/2022 7:51:18 PM         374.2             -85.382             715.8             -30.054         "ECAP"                 "Off"                         "Off"                     55.32      
    <missing>     10      6/Dec/2022 7:51:03 PM         374.2             -78.551               667              -31.42         "ECAP"                 "Off"                         "Off"                     47.13      
    <missing>     11      6/Dec/2022 7:50:48 PM         374.2             -70.696             715.8             -31.079         "ECAP"                 "Off"                         "Off"                     39.61      
    <missing>     12      6/Dec/2022 7:52:04 PM         374.2             -29.371               667             -15.368         "ECAP"                 "Off"                         "Off"                        14      
    <missing>     13      6/Dec/2022 7:53:33 PM         374.2             -21.516             715.8              -8.196         "ECAP"                 "Off"                         "Off"                     13.31      
    <missing>     14      6/Dec/2022 7:55:47 PM         374.2             -21.174             618.2             -14.002         "ECAP"                 "Off"                         "Off"                      7.17      
    <missing>     15      6/Dec/2022 7:53:48 PM         325.4             -17.418             715.8              -6.147         "ECAP"                 "Off"                         "Off"                     11.27      
    <missing>     16      6/Dec/2022 7:54:41 PM         325.4             -16.734             618.2              -8.538         "ECAP"                 "Off"                         "Off"                      8.19      

opts = detectImportOptions("P50-DE-test-6m...-IPG7-e3.xlsx","Sheet","NRT Peaks","TextType","string");
T2 = readtable("P50-DE-test-6m...-IPG7-e3.xlsx",opts);

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T2 = formatTbl(T2)

T2 = 28×11 table
     Status      NRTNr      RecordingDate_Time      NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    _________    _____    ______________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    <missing>      1      23/May/2023 7:32:03 PM         394.2             -79.234             833.4              39.275         "ECAP"                 "Off"                         "Off"                     118.5      
    <missing>      2      23/May/2023 7:39:01 PM         394.2             -64.207             882.2              53.278         "ECAP"                 "Off"                         "Off"                    117.48      
    <missing>      3      23/May/2023 7:32:19 PM         394.2             -72.745             882.2              44.057         "ECAP"                 "Off"                         "Off"                     116.8      
    <missing>      4      23/May/2023 7:32:34 PM         394.2             -80.942               931              34.153         "ECAP"                 "Off"                         "Off"                    115.09      
    <missing>      5      23/May/2023 7:32:49 PM         345.4             -81.967             833.4              33.811         "ECAP"                 "Off"                         "Off"                    115.77      
    <missing>      6      23/May/2023 7:33:04 PM         345.4              -78.21             882.2              43.374         "ECAP"                 "Off"                         "Off"                    121.58      
    <missing>      7      23/May/2023 7:39:51 PM         345.4             -77.868               931              31.762         "ECAP"                 "Off"                         "Off"                    109.62      
    <missing>      8      23/May/2023 7:38:30 PM         345.4              -78.21               931               29.03         "ECAP"                 "Off"                         "Off"                    107.23      
    <missing>      9      23/May/2023 8:10:37 PM         345.4             -96.311             882.2              18.101         "ECAP"                 "Off"                         "Off"                    114.41      
    <missing>     10      23/May/2023 7:35:42 PM         345.4             -97.677             833.4              18.784         "ECAP"                 "Off"                         "Off"                    116.46      
    <missing>     11      23/May/2023 7:39:24 PM         345.4             -96.311             882.2              18.784         "ECAP"                 "Off"                         "Off"                    115.09      
    <missing>     12      23/May/2023 8:10:52 PM         345.4             -97.677             833.4               4.781         "ECAP"                 "Off"                         "Off"                    102.45      
    <missing>     13      23/May/2023 7:33:20 PM         345.4             -96.653             833.4               1.707         "ECAP"                 "Off"                         "Off"                     98.36      
    <missing>     14      23/May/2023 8:11:07 PM         345.4             -88.456             735.8              -3.756         "ECAP"                 "Off"                         "Off"                     84.69      
    <missing>     15      23/May/2023 7:35:01 PM         345.4             -82.991             784.6              -5.464         "ECAP"                 "Off"                         "Off"                     77.52      
    <missing>     16      23/May/2023 7:33:35 PM         345.4             -56.352             735.8              -8.538         "ECAP"                 "Off"                         "Off"                     47.81      

function Tout = formatTbl(Tin)
if strcmp(class(Tin.RecordingDate_Time),"string")
    Tin.RecordingDate_Time = datetime(Tin.RecordingDate_Time,"InputFormat","d/M/uuuu H:mm:s","Format","d/MMM/uuuu h:mm:ss aa");
elseif strcmp(class(Tin.RecordingDate_Time),"datetime")
    [y,M,d] = ymd(Tin.RecordingDate_Time);
    [h,m,s] = hms(Tin.RecordingDate_Time);
    Tin.RecordingDate_Time = datetime(y,d,M,h,m,s,"Format","d/MMM/uuuu h:mm:ss aa");
end    
Tout = Tin;
end

[Rachel Scheperle]

This is terrific. Thanks so much. I modified the code slightly because of how I'm looping across files (and I only need year, month, and day) but so far the "if" "elseif" logic and flip-flopping the "d" and "M" inputs for datetime have addressed the issue in the small set of test files.

Answer 2

[Cris LaPierre]

The 'InputFormat' argument is only useful for date strings. Excel applies a display format to dates and times, so what you see is not actually how the data gets imported into MATLAB. In Excel, change the format of your date cells to General to view how the data is actually stored. That date and time should appear as 44901,8310069444

You might want to try setting the Datetime Type to exceldatenum.

opts = detectImportOptions(filepath,'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time","DatetimeType","exceldatenum",'DatetimeLocale','de_BE','DatetimeFormat','d/MM/uuuu h:mm:ss aa');
nrtPeaks = readtable(filepath,opts,'Sheet','NRT Peaks');

Note that the DatetimeLocale value determines how input text values are interpreted. Since the date is likely imported as a datenum, this setting is not doing anything and can probably be removed without affecting your output.

[Rachel Scheperle]

This sounds promising, but I'm getting an error with the 2nd line. Can you confirm that both "DatetimeType" and "exceldatenum" should be in double quotes?

[Voss]

@Rachel Scheperle: Here's a modification to Cris's answer that avoids the error and does what I think was intended. However, the dates are still June 12th instead of December 6th.

filepath = 'P50-DE-test-2w-AGF-IPG7-e3.xlsx';
opts = detectImportOptions(filepath,'Sheet','NRT Peaks','DatetimeType','exceldatenum');
opts = setvaropts(opts,"RecordingDate_Time",'Type','datetime','DatetimeLocale','de_BE','DatetimeFormat','d/MM/uuuu h:mm:ss aa');
T = readtable(filepath,opts,'Sheet','NRT Peaks')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 20x11 table
      Status      NRTNr     RecordingDate_Time      NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    __________    _____    _____________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    {0x0 char}      1      12/06/2022 7:56:39 PM         325.4             -147.54             813.4             -23.224        {'ECAP'}               {'Off'}                       {'Off'}                   124.31      
    {0x0 char}      2      12/06/2022 7:56:54 PM         374.2              -147.2             715.8             -38.251        {'ECAP'}               {'Off'}                       {'Off'}                   108.94      
    {0x0 char}      3      12/06/2022 7:57:10 PM         374.2             -148.56             715.8             -37.226        {'ECAP'}               {'Off'}                       {'Off'}                   111.33      
    {0x0 char}      4      12/06/2022 7:57:25 PM         374.2              -143.1             715.8             -39.275        {'ECAP'}               {'Off'}                       {'Off'}                   103.82      
    {0x0 char}      5      12/06/2022 7:57:40 PM         374.2             -134.56             764.6             -36.885        {'ECAP'}               {'Off'}                       {'Off'}                    97.67      
    {0x0 char}      6      12/06/2022 7:58:02 PM         325.4             -132.17             813.4             -35.177        {'ECAP'}               {'Off'}                       {'Off'}                    96.99      
    {0x0 char}      7      12/06/2022 7:51:49 PM         374.2             -106.56               667             -39.617        {'ECAP'}               {'Off'}                       {'Off'}                    66.94      
    {0x0 char}      8      12/06/2022 7:51:33 PM         374.2             -90.163             715.8             -34.494        {'ECAP'}               {'Off'}                       {'Off'}                    55.66      
    {0x0 char}      9      12/06/2022 7:51:18 PM         374.2             -85.382             715.8             -30.054        {'ECAP'}               {'Off'}                       {'Off'}                    55.32      
    {0x0 char}     10      12/06/2022 7:51:03 PM         374.2             -78.551               667              -31.42        {'ECAP'}               {'Off'}                       {'Off'}                    47.13      
    {0x0 char}     11      12/06/2022 7:50:48 PM         374.2             -70.696             715.8             -31.079        {'ECAP'}               {'Off'}                       {'Off'}                    39.61      
    {0x0 char}     12      12/06/2022 7:52:04 PM         374.2             -29.371               667             -15.368        {'ECAP'}               {'Off'}                       {'Off'}                       14      
    {0x0 char}     13      12/06/2022 7:53:33 PM         374.2             -21.516             715.8              -8.196        {'ECAP'}               {'Off'}                       {'Off'}                    13.31      
    {0x0 char}     14      12/06/2022 7:55:47 PM         374.2             -21.174             618.2             -14.002        {'ECAP'}               {'Off'}                       {'Off'}                     7.17      
    {0x0 char}     15      12/06/2022 7:53:48 PM         325.4             -17.418             715.8              -6.147        {'ECAP'}               {'Off'}                       {'Off'}                    11.27      
    {0x0 char}     16      12/06/2022 7:54:41 PM         325.4             -16.734             618.2              -8.538        {'ECAP'}               {'Off'}                       {'Off'}                     8.19      

month(T.RecordingDate_Time)

ans = 20x1

6 6 6 6 6 6 6 6 6 6

Are you sure that the error is not with the software that exports this data? You can see that the RecordingDate_Time data stored in the file really does correspond to June 12th, 2022, if it is 'excel' time type (but maybe it is another type):

opts = detectImportOptions(filepath,'Sheet','NRT Peaks','DatetimeType','exceldatenum');
T = readtable(filepath,opts,'Sheet','NRT Peaks')

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 20x11 table
      Status      NRTNr    RecordingDate_Time    NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    __________    _____    __________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    {0x0 char}      1            44725                325.4             -147.54             813.4             -23.224        {'ECAP'}               {'Off'}                       {'Off'}                   124.31      
    {0x0 char}      2            44725                374.2              -147.2             715.8             -38.251        {'ECAP'}               {'Off'}                       {'Off'}                   108.94      
    {0x0 char}      3            44725                374.2             -148.56             715.8             -37.226        {'ECAP'}               {'Off'}                       {'Off'}                   111.33      
    {0x0 char}      4            44725                374.2              -143.1             715.8             -39.275        {'ECAP'}               {'Off'}                       {'Off'}                   103.82      
    {0x0 char}      5            44725                374.2             -134.56             764.6             -36.885        {'ECAP'}               {'Off'}                       {'Off'}                    97.67      
    {0x0 char}      6            44725                325.4             -132.17             813.4             -35.177        {'ECAP'}               {'Off'}                       {'Off'}                    96.99      
    {0x0 char}      7            44725                374.2             -106.56               667             -39.617        {'ECAP'}               {'Off'}                       {'Off'}                    66.94      
    {0x0 char}      8            44725                374.2             -90.163             715.8             -34.494        {'ECAP'}               {'Off'}                       {'Off'}                    55.66      
    {0x0 char}      9            44725                374.2             -85.382             715.8             -30.054        {'ECAP'}               {'Off'}                       {'Off'}                    55.32      
    {0x0 char}     10            44725                374.2             -78.551               667              -31.42        {'ECAP'}               {'Off'}                       {'Off'}                    47.13      
    {0x0 char}     11            44725                374.2             -70.696             715.8             -31.079        {'ECAP'}               {'Off'}                       {'Off'}                    39.61      
    {0x0 char}     12            44725                374.2             -29.371               667             -15.368        {'ECAP'}               {'Off'}                       {'Off'}                       14      
    {0x0 char}     13            44725                374.2             -21.516             715.8              -8.196        {'ECAP'}               {'Off'}                       {'Off'}                    13.31      
    {0x0 char}     14            44725                374.2             -21.174             618.2             -14.002        {'ECAP'}               {'Off'}                       {'Off'}                     7.17      
    {0x0 char}     15            44725                325.4             -17.418             715.8              -6.147        {'ECAP'}               {'Off'}                       {'Off'}                    11.27      
    {0x0 char}     16            44725                325.4             -16.734             618.2              -8.538        {'ECAP'}               {'Off'}                       {'Off'}                     8.19      

datetime(T.RecordingDate_Time,'ConvertFrom','excel')

ans = 20x1 datetime array
   12-Jun-2022 19:56:39
   12-Jun-2022 19:56:54
   12-Jun-2022 19:57:10
   12-Jun-2022 19:57:25
   12-Jun-2022 19:57:40
   12-Jun-2022 19:58:02
   12-Jun-2022 19:51:49
   12-Jun-2022 19:51:33
   12-Jun-2022 19:51:18
   12-Jun-2022 19:51:03
   12-Jun-2022 19:50:48
   12-Jun-2022 19:52:04
   12-Jun-2022 19:53:33
   12-Jun-2022 19:55:47
   12-Jun-2022 19:53:48
   12-Jun-2022 19:54:41
   12-Jun-2022 19:54:03
   12-Jun-2022 19:55:20
   12-Jun-2022 19:54:18
   12-Jun-2022 19:52:34

[Cris LaPierre]

First, are you sure the format is set correctly? It may be due to the settings of my Excel, but when I open your spreadsheet and check the cell format, it is set to m/d/yyyy:

About quotes, when in doublt, use single quotes. They always work.

Sorry about the error. Without your file, I didn't have a quick way to check. While the 'DatetimeType' is a Name,Value pair for readtable, it does not seem to be available when using detectImportOptions. However, it does not appear to do what I was hoping anyway.

[Cris LaPierre]

If I modify the regional settings of the dates on that sheet so that dates are day/month, then it imports correctly. For me, that also meant re-entering the dates, since the Excel datenum was already recorded as Jun 12, 2022.

I modified the display format so that it is obvious the month and day are read in correctly.

opts = detectImportOptions("P50-DE-test-2w-AGF-IPG7-e3_edited.xlsx",'Sheet','NRT Peaks');
opts = setvaropts(opts,"RecordingDate_Time","DatetimeFormat","d/MMM/uuuu h:mm:ss aa");

T = readtable("P50-DE-test-2w-AGF-IPG7-e3_edited.xlsx",opts)

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T = 20×11 table
      Status      NRTNr     RecordingDate_Time      NPeakLatency__s_    NPeakValue__V_    PPeakLatency__s_    PPeakValue__V_    FrameType    FIRFilterCutOffFrequency_Hz_    HighResolutionCorrection    N1_P1Amplitude__V_
    __________    _____    _____________________    ________________    ______________    ________________    ______________    _________    ____________________________    ________________________    __________________

    {0×0 char}      1      6/Dec/2022 7:56:38 PM         325.4             -147.54             813.4             -23.224        {'ECAP'}               {'Off'}                       {'Off'}                   124.31      
    {0×0 char}      2      6/Dec/2022 7:56:54 PM         374.2              -147.2             715.8             -38.251        {'ECAP'}               {'Off'}                       {'Off'}                   108.94      
    {0×0 char}      3      6/Dec/2022 7:57:10 PM         374.2             -148.56             715.8             -37.226        {'ECAP'}               {'Off'}                       {'Off'}                   111.33      
    {0×0 char}      4      6/Dec/2022 7:57:24 PM         374.2              -143.1             715.8             -39.275        {'ECAP'}               {'Off'}                       {'Off'}                   103.82      
    {0×0 char}      5      6/Dec/2022 7:57:40 PM         374.2             -134.56             764.6             -36.885        {'ECAP'}               {'Off'}                       {'Off'}                    97.67      
    {0×0 char}      6      6/Dec/2022 7:58:02 PM         325.4             -132.17             813.4             -35.177        {'ECAP'}               {'Off'}                       {'Off'}                    96.99      
    {0×0 char}      7      6/Dec/2022 7:51:49 PM         374.2             -106.56               667             -39.617        {'ECAP'}               {'Off'}                       {'Off'}                    66.94      
    {0×0 char}      8      6/Dec/2022 7:51:33 PM         374.2             -90.163             715.8             -34.494        {'ECAP'}               {'Off'}                       {'Off'}                    55.66      
    {0×0 char}      9      6/Dec/2022 7:51:18 PM         374.2             -85.382             715.8             -30.054        {'ECAP'}               {'Off'}                       {'Off'}                    55.32      
    {0×0 char}     10      6/Dec/2022 7:51:02 PM         374.2             -78.551               667              -31.42        {'ECAP'}               {'Off'}                       {'Off'}                    47.13      
    {0×0 char}     11      6/Dec/2022 7:50:48 PM         374.2             -70.696             715.8             -31.079        {'ECAP'}               {'Off'}                       {'Off'}                    39.61      
    {0×0 char}     12      6/Dec/2022 7:52:04 PM         374.2             -29.371               667             -15.368        {'ECAP'}               {'Off'}                       {'Off'}                       14      
    {0×0 char}     13      6/Dec/2022 7:53:33 PM         374.2             -21.516             715.8              -8.196        {'ECAP'}               {'Off'}                       {'Off'}                    13.31      
    {0×0 char}     14      6/Dec/2022 7:55:47 PM         374.2             -21.174             618.2             -14.002        {'ECAP'}               {'Off'}                       {'Off'}                     7.17      
    {0×0 char}     15      6/Dec/2022 7:53:47 PM         325.4             -17.418             715.8              -6.147        {'ECAP'}               {'Off'}                       {'Off'}                    11.27      
    {0×0 char}     16      6/Dec/2022 7:54:41 PM         325.4             -16.734             618.2              -8.538        {'ECAP'}               {'Off'}                       {'Off'}                     8.19      

[Rachel Scheperle]

I appreciate all of your efforts and glad to know what is at the root of the problem. I will be reaching out to the software engineers of to address how the excel sheet is being written.

Since my part of the process is down-stream of when the excel sheet gets created, I was hoping to find a work around to deal with all of the files we currently have that were exported over the past year.