Formula to auto calculate time difference in hours between two 24hr format times?
I'm looking for a formula to help me calculate the length of hours an individual has worked. Employees enter their "Haul Start Time" and "Haul End Time" manually by picking from a drop down list of times in 24hr format.
Once picking their starting time and end time, I would like the third column, "Total Hours", to automatically calculate the difference in hours. Right now we are having to manually do the calculation and enter the hours worked. For example I would like the format to be in: 1, 1.25, 1.5 , 1.75 , 2.00 , 2.25 , etc. for the hours worked.
I have attached an image of the columns. Please let me know if you need any other pictures or explanation.
Thank You in advance!
Best Answer
-
@Justin Runner Give THIS thread a read through...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hi
As of now smartsheet does not offer this feature to "automatically" populate a cell with some predefined formula.
The only alternative available is that you copy-paste the "total hours" formula in the entire third column (or in as many row cells as you feel like).
With this, whenever someone fills in the first 2 column, the 3rd one will instantly show the time difference.
Hope this helps!
best
Nasir
-
Hi @Justin Runner,
To add to Nasir's excellent advice.
If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.
Conditions That Trigger Formula Autofill
You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:
- Directly between two others that contain the same formula in adjacent cells.
- At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
- At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
- Above or below a single row that is between blank rows and has formulas.
More info:
Would that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi @Andrée Starå and @Nasir@EBC,
Thank you for your response! I apologize for not being clear enough in my question. I need helping coming up with the formula for the "Total Hours" column. There is currently no formula at all. We are currently manually doing the calculations to find the total hours worked based on the employees "Haul Start Time" and "Haul End Time" and then manually entering the data into the column. I hope this helps and clears things up.
Looking forward to hearing back!
Thank You
-
@Justin Runner Give THIS thread a read through...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This worked perfectly! Thank you so much!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No worries!
Happy to help!
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!