Time formula

Can anyone assist with writing a formula using military hours? See example sheet fields below.
Best Answer
-
Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:
=((VALUE(LEFT([Time Out]@row, FIND(":", [Time Out]@row) - 1)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) + ([Date Ended]@row - [Date Started]@row) * 24) - (VALUE(LEFT([Time In]@row, FIND(":", [Time In]@row) - 1)) + VALUE(RIGHT([Time In]@row, 2)) / 60)
Now use the following formula in your Total Time column:
=INT(Duration@row) + ":" + IF((Duration@row - INT(Duration@row)) * 60 < 10, "0") + (Duration@row - INT(Duration@row)) * 60Senior Business Intelligence Analyst
Augmedix Bangladesh
(A Commure Company)
shimanta@augmedix.com
Answers
-
Add a helper column for calculating the duration time in decimal. Name the column as Duration. Now apply the following formula in the Duration column:
=((VALUE(LEFT([Time Out]@row, FIND(":", [Time Out]@row) - 1)) + VALUE(RIGHT([Time Out]@row, 2)) / 60) + ([Date Ended]@row - [Date Started]@row) * 24) - (VALUE(LEFT([Time In]@row, FIND(":", [Time In]@row) - 1)) + VALUE(RIGHT([Time In]@row, 2)) / 60)
Now use the following formula in your Total Time column:
=INT(Duration@row) + ":" + IF((Duration@row - INT(Duration@row)) * 60 < 10, "0") + (Duration@row - INT(Duration@row)) * 60Senior Business Intelligence Analyst
Augmedix Bangladesh
(A Commure Company)
shimanta@augmedix.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!