Formula to auto calculate time difference in hours between two 24hr format times?

Justin Runner

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!

Paul Newcome

@Justin Runner Give THIS thread a read through...

Nasir@EBC

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

Andrée Starå

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: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

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!

Justin Runner

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

Paul Newcome

@Justin Runner Give THIS thread a read through...

Justin Runner

@Paul Newcome

This worked perfectly! Thank you so much!

Paul Newcome

Happy to help! 👍️

Andrée Starå

@Justin Runner

No worries!

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée