Can this formula be fixed to get the correct time?
Hello,
I am trying to come up with a formula to track time in Smartsheet. This is the formula I have:
=IF([End Time]@row < [Start Time]@row, ((1200 + [End Time]@row)  [Start Time]@row) * 0.01, (([End Time]@row  [Start Time]@row) * 0.01))
After we hit a certain hour it tacks on 40 extra minutes. Is there a way to fix this formula so that it does not add the extra 40 minutes?
Thank you,
Larissa
Answers

Here is a thread with quite a few time based solutions...

Thank you, but is there a way to fix the formula I have?

Are you able to provide a screenshot that has a couple of rows that are working properly and a couple of rows that are not?

The row in white is the formula working properly, and the row in red is the formula not working properly. The red row is adding an additional 40 minutes.
Thank you,
Larissa

That is because you are going into the next hour, but your formula is calculating numbers instead of time. Numbers go to 100. Hours go to 60. That's where the extra 40 minutes is going.
You are going to want to multiply each time by 60, subtract the start from the end, then divide the total by 60.
=IF([End Time]@row < [Start Time]@row, (((1200 + [End Time]@row) * 60)  ([Start Time]@row * 60)) / 60, (([End Time]@row * 60)  ([Start Time]@row * 60)) / 60)

That formula still adds on the extra 40 minutes.
Larissa

Hi Larissa, if you are willing to share your sheet in view mode to me, or copy this sheet and share me to the copy of the duplicate sheet... I can try and work this out real quick for you.
Let me know if you are and I'll drop you my work email address.
Certifications:
Smartsheet 2023 Core Product Certification
Smartsheet 2023 System Administrator Certification

Ok. You are going to need a different formula instead of trying to adjust your existing formula.
=(((VALUE(LEFT([End time]@row, LEN([End Time]@row)  2)) * 60) + IF([End Time]@row < [Start Time]@row, 1440) + VALUE(RIGHT([End Time]@row, 2)))  ((VALUE(LEFT([Start time]@row, LEN([Start Time]@row)  2)) * 60) + VALUE(RIGHT([Start Time]@row, 2)))) / 60
Help Article Resources
Categories
Check out the Formula Handbook template!