Excel sheet time data defaulting to January 1st, 1970 on plot

I am importing excel sheeting to MatLab with time (MM-dd-yyyy HH-mm) in the first column and voltage data in the second column. When I go to plot this data, the x-axis (time) defaults to January 1st, 1970. How do I go about fixing this? The date my excel data starts on is December 1st, 2021.

I have been using the datetime function but think it may need to be tweaked.

[Steven Lord]

Please show us how you imported the data from the spreadsheet and how you used datetime as part of the process of preparing your data. Did you use readtable on your spreadsheet? If not did you use datetime to 'ConvertFrom', 'Excel' the data you read from the file?

Answer 1

[Star Strider]

My best guess is that datetime is not interpreting the time column correctly.

It would need to be something like this:

time = '12-01-2021 00-01'

time = '12-01-2021 00-01'

dttime = datetime (time, 'InputFormat','MM-dd-yyyy HH-mm', 'Format','yyyy/MM/dd HH:mm')

dttime = datetime
   2021/12/01 00:01

Use the appropriate string for the 'Format' name-value pair depending on how the result is to be displayed.

.

[Stephen23]

"My best guess is that datetime is not interpreting the time column correctly."

Excel worksheets stores dates as a double (serial date number, days since 1900, incorrectly identified as a leap year), so formatting of an actual date is totally irrelevant (being only an artifact of the locale settings). Ergo, if the formatting makes any difference to importing, then it is not stored as a date in Excel.

If it was, this problem would most likely disappear entirely.

In any case, the 1st of January 1970 is the Unix epoch, which hints that there a bigger issues involved...

[notalwayssure]

I believe I have found the issue. Hidden in the thousands of rows of data, I have random rows like the one shown in bold below. I went through and manually deleted them and the code runs smoothly. Must have stemmed from software output during testing.

12/20/2021 13:10:00

12/20/2021 13:10:01

12/20/2021 13:10:02

01/11/1970 15:40:00

12/20/2021 13:10:03

Is there a way (maybe an if..then loop) I could neglect all of the rows that have data in the year 1970 so that the code passes over them and I don't have to do a manual deletion in Excel.

[Star Strider]

I would use ‘logical indexing’ to eliminate the 1970 values —

time = ['12/20/2021 13:10:00'
        '12/20/2021 13:10:01'
        '12/20/2021 13:10:02'
        '01/11/1970 15:40:00' 
        '12/20/2021 13:10:03'];
dt_time = datetime(time, 'InputFormat','MM/dd/yyyy HH:mm:ss')

dt_time = 5×1 datetime array
   20-Dec-2021 13:10:00
   20-Dec-2021 13:10:01
   20-Dec-2021 13:10:02
   11-Jan-1970 15:40:00
   20-Dec-2021 13:10:03

year_not_1970 = year(dt_time) ~= 1970;
dt_time_new = dt_time(year_not_1970)

dt_time_new = 4×1 datetime array
   20-Dec-2021 13:10:00
   20-Dec-2021 13:10:01
   20-Dec-2021 13:10:02
   20-Dec-2021 13:10:03

Using this in a table would require a second dimension reference —

T = table(dt_time, randn(size(dt_time)))

T = 5×2 table
          dt_time            Var2  
    ____________________    _______

    20-Dec-2021 13:10:00     1.0861
    20-Dec-2021 13:10:01    0.13509
    20-Dec-2021 13:10:02     1.1776
    11-Jan-1970 15:40:00     1.4696
    20-Dec-2021 13:10:03    0.37878

T_new = T(year_not_1970,:)

T_new = 4×2 table
          dt_time            Var2  
    ____________________    _______

    20-Dec-2021 13:10:00     1.0861
    20-Dec-2021 13:10:01    0.13509
    20-Dec-2021 13:10:02     1.1776
    20-Dec-2021 13:10:03    0.37878

or something similar.

.