Extract the 'hour' out of a date format

Options

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

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!