Converting date and time cell array to date format

Hey All,

I have a script that pulls an excel file.

The excel file has a column for dates, the date are pulled as a cell array because, i think, they contain "AM EST/PM EST".

I've tried several commands to convert it to a format suitable for comparison to another date array.

In the end I hope to compare this date and time array to a date and time interval in order to find out if this time stamp happened in between the interval.

%import excel 
excel = uigetfile('*.xls;*.xlsx;*.csv');
encounters = readtable(excel);
encounters.jointime

ans =

  211×1 cell array

    '12/19/2018 09:01 AM EST'
    '12/19/2018 09:03 AM EST'
    '12/19/2018 09:05 AM EST'
    '12/19/2018 09:35 AM EST'
    '12/19/2018 09:50 AM EST'
    '12/19/2018 10:00 AM EST'
    '12/19/2018 10:06 AM EST'
    '12/19/2018 10:06 AM EST'
    '12/19/2018 10:19 AM EST'
    '12/19/2018 10:21 AM EST'
    '12/19/2018 10:30 AM EST'
    '12/19/2018 10:46 AM EST'
    '12/19/2018 10:57 AM EST'
    '12/19/2018 11:02 AM EST'
    '12/19/2018 11:03 AM EST'
    '12/19/2018 11:34 AM EST'
    '12/19/2018 11:43 AM EST'
    '12/19/2018 11:44 AM EST'
    '12/19/2018 12:25 PM EST'
     ...

Answer 1

[Walter Roberson]

datetime(encounters.jointime, 'InputFormat', 'MM/dd/yyyy hh:mm a z', 'TimeZone', 'UTC')

Here, in place of UTC, you should use your default timezone. If you specify something like 'America/New_York' be aware that you are converting to times that take into account daylight savings.

You need to decide whether you want localtimes or standard time, which is going to depend upon what you are going to do with the data.

[Juan Moreta]

Thanks for your response Walter.

What if 'encounters.jointime' consists of different timezones? Will UTC equate all of them?

Thanks!

[Peter Perkins]

There's a difference between the time zone OFFSET that's in the timestamps, and the time zone that you assign to your result. If you are converting timestamps that contain a tz offset, you must specify a timezone for the result, which Walter has shown. What tz you specify, as Walter says, is up to you.

But to answer your question, datetime will automnatically convert the timestamps using their offset. So if some of your timestamps had EST, while others had CST, datetime would automatically account for that.

>> datetime(["1-Jan-2019 09:30:00 EST" "1-Jan-2019 09:30:00 CST"],"Format","dd-MMM-yyyy HH:mm:ss z","TimeZone","America/New_York")
ans = 
  1×2 datetime array
   01-Jan-2019 09:30:00 EST   01-Jan-2019 10:30:00 EST