# Can this formula be fixed to get the correct time?

Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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?

• Options

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

• ✭✭✭✭✭✭
Options

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)

• Options

That formula still adds on the extra 40 minutes.

Larissa

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!