Can this formula be fixed to get the correct time?

Larissa Motts · 07/27/21

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 · 07/27/21

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

I have decided to create a post that consolidates all of the different time based solutions that I have put together.<\/p>

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.<\/p>

I thought it would be helpful to have them all in one place instead of spread out.<\/p>

I welcome all feedback, suggestions, and additions.<\/p>

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.<\/p>

I will also try to post various tips and tricks as I think of them as well such as:<\/p>

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:<\/p>

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))<\/p>

Calculating Time Worked for Employees<\/a><\/p>

Can you calculate time in Smartsheet?<\/a><\/p>

Conversion of timezones (Solved Formula Included)<\/a><\/p>

Date\/Time\/Year<\/a><\/p>

Flagging a Date and Time Overlap<\/a><\/p>

How do I create time of day columns?<\/a><\/p>

Need to create a "shift" column from a time column<\/a><\/p>","bodyRaw":"[{\"insert\":\"I have decided to create a post that consolidates all of the different time based solutions that I have put together.\\nSome of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.\\n\\nI thought it would be helpful to have them all in one place instead of spread out.\\n\\nI welcome all feedback, suggestions, and additions.\\n\\nI'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.\\n\\nI will also try to post various tips and tricks as I think of them as well such as:\\nIt is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:\\n=VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) + IF(CONTAINS(\\\"p\\\", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(\\\":\\\", [Time Column]@row) - 1)) = 12, -12))\\n\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=bde3993a5b7e408e9f112d2049f76662\"},\"insert\":\"Calculating Time Worked for Employees\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc\"},\"insert\":\"Can you calculate time in Smartsheet?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=0122690b7ff744c68653e4dd0fa3cd81\"},\"insert\":\"Conversion of timezones (Solved Formula Included)\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=8b6ddddb66bb4b3db6fbbec745b46795\"},\"insert\":\"Date\\\/Time\\\/Year\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=d39961b785de4312ba2c98b8d148b3bd\"},\"insert\":\"Flagging a Date and Time Overlap\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767\"},\"insert\":\"How do I create time of day columns?\"},{\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/app.smartsheet.com\\\/b\\\/publish?EQBCT=54f270e0ed1440f6a25de2453119655c\"},\"insert\":\"Need to create a \\\"shift\\\" column from a time column\"},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2020-06-08T15:55:08+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T20:54:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/68947\/formulas-for-calculating-time","embedType":"quote","name":"Formulas for Calculating Time"}"> https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Larissa Motts · 07/28/21

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

Paul Newcome · 07/28/21

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 · 07/29/21

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 · 07/30/21

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 · 08/03/21

That formula still adds on the extra 40 minutes.

Larissa

StevenBlackburnMBA · 08/03/21

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 · 08/03/21

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

Can this formula be fixed to get the correct time?

Answers

Help Article Resources

Categories