Can this formula be fixed to get the correct time?

Larissa Motts

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

Paul Newcome

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

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Larissa Motts

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

Paul Newcome

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?

Larissa Motts

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

Paul Newcome

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)

Larissa Motts

That formula still adds on the extra 40 minutes.

Larissa

StevenBlackburnMBA

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.

Paul Newcome

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