Return the last time in a datetime column containing NaT

Hi, I'm trying to get the value of the last recognised time entry in an imported excel column of data. At a point, the column changes from datetime values to NaT values, I think due to some of the other columns being longer than my datetime column. It's not practical to edit the excel file to use a simple function. I have tried using find with ~isnat,but I'm not sure I'm applying it correctly. See code below and sample data attached. Any help is appreciated!

ReadSS = readtable('2columns.xlsx','Sheet','Trend Data')

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.

ReadSS = 28981×2 table
               DateAndTime               value
    _________________________________    _____

    {'08/02/2024 07:40:00.000000 PM'}       0 
    {'08/02/2024 07:40:00.100000 PM'}    0.04 
    {'08/02/2024 07:40:00.200000 PM'}    0.08 
    {'08/02/2024 07:40:00.300000 PM'}    0.12 
    {'08/02/2024 07:40:00.400000 PM'}    0.16 
    {'08/02/2024 07:40:00.500000 PM'}     0.2 
    {'08/02/2024 07:40:00.600000 PM'}    0.24 
    {'08/02/2024 07:40:00.700000 PM'}    0.28 
    {'08/02/2024 07:40:00.800000 PM'}    0.32 
    {'08/02/2024 07:40:00.900000 PM'}    0.36 
    {'08/02/2024 07:40:01.000000 PM'}     0.4 
    {'08/02/2024 07:40:01.100000 PM'}    0.44 
    {'08/02/2024 07:40:01.200000 PM'}    0.48 
    {'08/02/2024 07:40:01.300000 PM'}    0.52 
    {'08/02/2024 07:40:01.400000 PM'}    0.56 
    {'08/02/2024 07:40:01.500000 PM'}     0.6 

TimeCol = datetime(ReadSS{:,1}, 'InputFormat', 'dd/MM/yyyy hh:mm:ss.SSSSSS a ''', 'TimeZone', 'UTC');   %identify the format of time and date in excel column 1
TimeCol.Format = 'dd/MM/uuuu HH:mm:ss.SSSSSS';                                                          %Set the format of the time data in matlab

first_ele=TimeCol(1,:)                      % first value

first_ele = datetime
   08/02/2024 19:40:00.000000

last_ele=TimeCol(end,:)                     %last value

last_ele = datetime
   NaT

last_ele= find(~isnat(TimeCol(end,:)))

last_ele =

     []

Answer 1

[Dyuman Joshi]

Your data is already a column, using 1 and end as indices on it will provide scalars (see the edit above).

ReadSS = readtable('2columns.xlsx','Sheet','Trend Data')

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.

ReadSS = 28981×2 table
               DateAndTime               value
    _________________________________    _____

    {'08/02/2024 07:40:00.000000 PM'}       0 
    {'08/02/2024 07:40:00.100000 PM'}    0.04 
    {'08/02/2024 07:40:00.200000 PM'}    0.08 
    {'08/02/2024 07:40:00.300000 PM'}    0.12 
    {'08/02/2024 07:40:00.400000 PM'}    0.16 
    {'08/02/2024 07:40:00.500000 PM'}     0.2 
    {'08/02/2024 07:40:00.600000 PM'}    0.24 
    {'08/02/2024 07:40:00.700000 PM'}    0.28 
    {'08/02/2024 07:40:00.800000 PM'}    0.32 
    {'08/02/2024 07:40:00.900000 PM'}    0.36 
    {'08/02/2024 07:40:01.000000 PM'}     0.4 
    {'08/02/2024 07:40:01.100000 PM'}    0.44 
    {'08/02/2024 07:40:01.200000 PM'}    0.48 
    {'08/02/2024 07:40:01.300000 PM'}    0.52 
    {'08/02/2024 07:40:01.400000 PM'}    0.56 
    {'08/02/2024 07:40:01.500000 PM'}     0.6 

%You can include the output format in datetime() call 
TimeCol = datetime(ReadSS{:,1}, 'InputFormat', 'dd/MM/yyyy hh:mm:ss.SSSSSS a ''', 'TimeZone', 'UTC', ...
    'Format', 'dd/MM/uuuu HH:mm:ss.SSSSSS');   %identify the format of time and date in excel column 1
%Set the format of the time data in matlab

Specify the direction of search i.e. last in the find() call -

%Find the last not-a-Time value in the given column data
last_ele= find(~isnat(TimeCol), 1, 'last')

last_ele = 12000

[Numbers]

Hi Dyuman, Thank you for answering and noting the output format in the datetime call. This find() call returns the number of datetime elements (12000). How do I get the value of the last datetime element i.e. the last date and time before the column changes to NaT (same format as the first_ele object) ?

[Voss]

"This find() call returns the number of datetime elements (12000)"

@Numbers: Note that the number of datetime elements is 28981, not 12000.

warning off all
ReadSS = readtable('2columns.xlsx','Sheet','Trend Data');
TimeCol = datetime(ReadSS{:,1}, ...
    'InputFormat', 'dd/MM/yyyy hh:mm:ss.SSSSSS a', ...
    'TimeZone', 'UTC', ...
    'Format', 'dd/MM/uuuu HH:mm:ss.SSSSSS');

numel(TimeCol)

ans = 28981

To get the last non-NaT datetime value rather than its index, simply index TimeCol with the index found in Dyuman's answer:

last_ele = TimeCol(find(~isnat(TimeCol), 1, 'last'))

last_ele = datetime
   08/02/2024 19:59:59.900000

[Numbers]

Hi Voss, I think it's 12000 datetime (20 mins x 60 secs x 10 msecs = 12000 entries) in column 1, with the rest of the column importing as NaT. The ~29000 entries are in column 2. So an issue is that column 2 is longer than column 1, so it seems that to import this data using a readtable array, Matlab fills column 1 with NaT. But regardless, your solution works, so thank you!

[Numbers]

Voss, I'm not sure how to accept your answer? Seems the most succint answer to my question as it was asked

[Voss]

I see what you're saying about the 12000 datetimes. Nevertheless, NaT is also a datetime, and in a table all columns must be the same length, so technically there are 28981 datetimes in column 1.

You should accept Dyuman Joshi's answer, as my response was merely a follow-up comment.

[Stephen23]

"I think it's 12000 datetime... with the rest of the column importing as NaT"

As Voss correctly wrote, NaT are also DATETIME objects. This is very easy to confirm:

isdatetime(NaT)

ans = logical
   1