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
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!