Dropping text and converting to DateTime?

[jwlam]

Hi everyone!

I'm trying to convert a string with time at the beginning into a DateTime field so I can use DateTimeDiff([Left At],[Joined], "minutes") to calculate the difference between when someone joined and left a meeting.

The data is always the time followed by am/pm and location (11:00 am San Francisco Time).

What I'm looking for is pulling out 11:00 and dropping the rest.

What I've tried is Text to Column to pull out only the 11:00 then Select to change to Time or DateTime. Both don't work in the DateTime tool when trying to convert to 11:00:00 to use the DateTimeDiff formula. It gives me a null value instead after changing the type.

When I summarize I get the data to come through

But when I use DateTime tool it returns as Null

[IraWatt]

Hey @jwlam,

There is a bit to do here. First add two zeros and a ':' before your times and the convert it to a time data type. Then you can use the date time diff function. However you will need to give a date so I just added in 2022-01-1 like in the example attached:

Any questions or issues please ask :)
HTH!
Ira

[DataNath · Accepted solution]

Can just calculate it from the strings you have parsed if you want to reduce the amount of tools for performance (don't need the DateTimes):

[binuacs]

@jwlam One way of dong this with the datetime functions

[jwlam]

Thanks everyone! I ended up partially using @DataNath's solution. I parsed out each individual column's information by using DateTimeParse([Column Name], '%I:%M %P').