Extract the 'hour' out of a date format

Nicola Ray

Hi there

I'm trying to pull out the start time of the meeting and the end time of a meeting so I can have the duration of a meeting - i.e. 1 hour.

The date format for a meeting is being pulled through like this: 2020-11-27T10:00:00Z

The end time for this meeting is 2020-11-27T11:00:00Z

I want to pull the duration of the meeting into another column (so it shows in a Calendar within Smartsheet). So far, I've been trying to split the information and pull out the 'hour' element for both and then subtract to get the duration. I suspect there's something that is more efficient though!

Is there a magic formula that would work for this?

Thanks

David Joyeuse

Hi @Nicola Ray

If the date formats your are showing are correct, then I would suggest running a MID function here:

=IFERROR(VALUE(MID([Starting Time]@row,12, 2)),"")

That should be working if Months and days are always displayed as 2 numbers like 03 or 07 not 3 and 7.

Otherwise you should run this:

=IFERROR(VALUE(MID([Starting Time]@row, FIND("T", @cell)+1, 2)),"")

This should cover every possibility.

The VALUE function is here to convert the string into number.

=IFERROR(VALUE(MID([End Time]@row, FIND("T", @cell)+1, 2)),"")

Will do it for the end time.

Hope it helped!