Formulas for Calculating Time
Comments
-
Paul Newcome ✭✭✭✭✭✭@Addison SpencerTheoretically 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?
是re breaks that need to be factored in?
Will the start time always be an even hour with no minutes?
thinkspi.com
-
Will the times ALWAYS start in the am? Most Likely but I couldn't say 100%
Would "Leave" be considered 0? Yes
是re 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%
是helper columns just to one side of the day columns, and that's what any extra info goes into?
-
Paul Newcome ✭✭✭✭✭✭
Adding aLINK TO PAGE 1 WITH ORIGINAL POST OF SOLUTIONSso that there is an easy way to jump to the main grouping.
thinkspi.com
-
Paul Newcome ✭✭✭✭✭✭
@Addison SpencerThe 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?
thinkspi.com
-
@Paul NewcomeThat 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 ✭✭✭✭✭✭
@Addison SpencerOk. 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.
thinkspi.com
-
@Paul NewcomeNot a problem! Thank you so much!!
-
Paul Newcome ✭✭✭✭✭✭
@Addison SpencerWill there be any times entered for the 12:00 hours such as 12:00pm, 12:30pm, 12:00am, 12:30am?
thinkspi.com
-
@Paul Newcomenot 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 ✭✭✭✭✭✭
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.
thinkspi.com
-
Paul Newcome ✭✭✭✭✭✭
HEREis 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([email protected]= "Leave", 0, ((VALUE(MID([email protected], FIND("-",[email protected]) + 1, FIND(":",[email protected], FIND("-",[email protected])) - (FIND("-",[email protected]) + 1))) + IF(RIGHT([email protected]) = "p", IF(VALUE(MID([email protected], FIND("-",[email protected]) + 1, FIND(":",[email protected], FIND("-",[email protected])) - (FIND("-",[email protected]) + 1))) <> 12, 12), IF(VALUE(MID([email protected], FIND("-",[email protected]) + 1, FIND(":",[email protected], FIND("-",[email protected])) - (FIND("-",[email protected]) + 1))) = 12, -12)) + VALUE(MID([email protected], LEN([email protected]) - 2, 2)) / 60) - (VALUE(LEFT([email protected], FIND(":",[email protected]) - 1)) + IF(MID([email protected], FIND("-",[email protected]) - 1, 1) = "p", IF(VALUE(LEFT([email protected], FIND(":",[email protected]) - 1)) <> 12, 12), IF(VALUE(LEFT([email protected], FIND(":",[email protected]) - 1)) = 12, -12)) + VALUE(MID([email protected], FIND(":",[email protected]) + 1, 2)) / 60)) - 0.5), "")
thinkspi.com
-
@Paul Newcomethank you so much!!!
-
Paul Newcome ✭✭✭✭✭✭
-
Paul Newcome ✭✭✭✭✭✭
@Lisa SmithI also posted a copy of your solution in this thread to try to keep all of my time solutions in one place...
工作时间没有日期重叠。午餐和Leave times subtracted. 12h times with no leading zero using "am" and "pm".
thinkspi.com
-
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.
