Extract the 'hour' out of a date format
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 153 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!