Formulas for Calculating Time

Paul Newcome · 11/23/20

@Addison Spencer Theoretically it can be done without a bunch of helper columns, but troubleshooting and building and maintaining can be rather cumbersome without them. I generally hid the helper columns so they are not visible/creating a "busy" screen.

Having said that, I would recommend at the very least a helper column for each day of the week. From there I would have a handful of questions.

Will the times ALWAYS start in the am?

Would "Leave" be considered 0?

Are there breaks that need to be factored in?

Will the start time always be an even hour with no minutes?

Addison Spencer · 11/23/20

https://community.smartsheet.com/discussion/comment/269759#Comment_269759

@Paul Newcome

Will the times ALWAYS start in the am? Most Likely but I couldn't say 100%

Would "Leave" be considered 0? Yes

Are there breaks that need to be factored in? Yes, 30 min

Will the start time always be an even hour with no minutes? Again most likely, but couldn't say 100%

Are the helper columns just to one side of the day columns, and that's what any extra info goes into?

Paul Newcome · 11/24/20

Adding a LINK TO PAGE 1 WITH ORIGINAL POST OF SOLUTIONS so that there is an easy way to jump to the main grouping.

Paul Newcome · 11/24/20

@Addison Spencer The helper columns can be put wherever you want them to be and you can also hide them from view. I generally stack all of my helper columns on the far right of the sheet, lock them, then hide them.

Based on your initial answers, I am going to suggest a minor formatting change and have at least one other question (possibly more as we build out the solution).

Suggested Formatting Change: Include the colon and minutes for ALL times so that 9a is now 9:00a, 10a is now 10:00a, so on and so forth. Consistency in data will make the formulas much easier to build and manage.

Additional Question: Will there be date overlap such as 9:00p - 4:30a?

Addison Spencer · 11/24/20

@Paul Newcome That makes sense - I was just copying the original format that someone else had put it so I can try that.

Will there be date overlap such as 9:00p - 4:30a? Nope. The times should always be between 9:00am and 5:30pm (It's a museum so no real crazy hours)

Paul Newcome · 11/24/20

@Addison Spencer Ok. It is going to take some time (pun intended) because my workload has been crazy lately, but I will see what we can put together. I am positive that it is possible. It is just going to take some testing and building to get the details right. I'll keep you posted.

Addison Spencer · 11/24/20

@Paul Newcome Not a problem! Thank you so much!!

Paul Newcome · 11/24/20

@Addison Spencer Will there be any times entered for the 12:00 hours such as 12:00pm, 12:30pm, 12:00am, 12:30am?

Addison Spencer · 11/24/20

@Paul Newcome not that I'm aware of.

Also lunch for everyone is at different times if that makes a difference, although it could be standard 30min in a formula or something since that's not really shown anywhere in the schedule, just assumed.

Paul Newcome · 11/24/20

The actual times for lunch are not important if it is assumed that it will always be 30 minutes. What I will probably end up doing is figuring out the entire duration and then just subtracting 0.5 hours from that end result.

Paul Newcome · 11/24/20

HERE is a link to a published sheet that contains a solution for you. This is built out with no helper columns so that the entire calculation is done in a single larger formula. Random times have been entered to account for various possibilities (even some that you said shouldn't happen) such as start and end in pm. Start and end in am. Start and end during the noon hour. Start during the midnight hour.

I used an IFERROR to output a blank in the event there is an error. This is primarily caused by a blank weekday cell.

I also adjusted the formatting so that all times are h:mm or hh:mm.

Looking at the sheet, the formula is at the bottom of the [Notes/Hyperlinks] column on the far left. You would dragfill this formula down rows and across columns.

Next is a column for the time entry for each day of the week, and finally is a column for each day of the week to capture the total time worked (minus a half hour break).

Feel free to let me know if you have any questions...

Here is the formula:

=IFERROR(IF(Sunday@row = "Leave", 0, ((VALUE(MID(Sunday@row, FIND("-", Sunday@row) + 1, FIND(":", Sunday@row, FIND("-", Sunday@row)) - (FIND("-", Sunday@row) + 1))) + IF(RIGHT(Sunday@row) = "p", IF(VALUE(MID(Sunday@row, FIND("-", Sunday@row) + 1, FIND(":", Sunday@row, FIND("-", Sunday@row)) - (FIND("-", Sunday@row) + 1))) <> 12, 12), IF(VALUE(MID(Sunday@row, FIND("-", Sunday@row) + 1, FIND(":", Sunday@row, FIND("-", Sunday@row)) - (FIND("-", Sunday@row) + 1))) = 12, -12)) + VALUE(MID(Sunday@row, LEN(Sunday@row) - 2, 2)) / 60) - (VALUE(LEFT(Sunday@row, FIND(":", Sunday@row) - 1)) + IF(MID(Sunday@row, FIND("-", Sunday@row) - 1, 1) = "p", IF(VALUE(LEFT(Sunday@row, FIND(":", Sunday@row) - 1)) <> 12, 12), IF(VALUE(LEFT(Sunday@row, FIND(":", Sunday@row) - 1)) = 12, -12)) + VALUE(MID(Sunday@row, FIND(":", Sunday@row) + 1, 2)) / 60)) - 0.5), "")

Addison Spencer · 11/24/20

@Paul Newcome thank you so much!!!

Paul Newcome · 11/24/20

@Addison Spencer Happy to help. 👍️

Paul Newcome · 12/11/20

@Lisa Smith I also posted a copy of your solution in this thread to try to keep all of my time solutions in one place...

Working hours with no date overlap. Lunch and Leave times subtracted. 12h times with no leading zero using "am" and "pm".

Royce Salisbury · 01/19/21

@Paul Newcome

Hi Paul,

Wondering if you have a solution for this.

Currently using Smartsheet to tracking incoming PO's and have a next follow up column so that i get a reminder each morning for and PO that need to be followed up.

Mostly once it is followed up, the next date more 1-3 days. However i would like to have the options to add a time, so that if i need to follow up at 3pm i would get a notification email at 3pm.

Formulas for Calculating Time

Comments

Categories