Formulas for Calculating Time
I have decided to create a post that consolidates all of the different time based solutions that I have put together.
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.
I thought it would be helpful to have them all in one place instead of spread out.
I welcome all feedback, suggestions, and additions.
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.
I will also try to post various tips and tricks as I think of them as well such as:
It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:
=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))
Calculating Time Worked for Employees
Can you calculate time in Smartsheet?
Conversion of timezones (Solved Formula Included)
Flagging a Date and Time Overlap
thinkspi.com
Comments
-
Paul Newcome ✭✭✭✭✭✭
@Genevieve PHere's that Time post we were talking about before.
@[email protected]I still haven't had a chance to put something together using the decimals from the created vs modified columns. If you'd like, feel free to post what you've put together. I'll post something of my own eventually. It's only a matter of... time...
@Andrée Staråand@Mike WildaySince you both are pretty active with providing solutions here in the community as well, I figure you might also be interested in this thread if you ever run across some time based requests.
I figure this might also help give the folks at Smartsheet a point of reference and an idea of different ways that time calculations are actually being used.
thinkspi.com
-
L_123 ✭✭✭✭✭✭I've been looking at that formula, and it seems off. I always get the hours correct, but the minutes seem to be incorrect after a period of time. I think instead of recording seconds in the decimal location they are recording something closer to ticks that are simply very close in measurement of time to seconds, so I hesitate to post that formula now. In my testing all of the results were same day which I don't believe gave it enough time to make the difference evident.
I am trying to figure out 2 things. Are the "ticks" consistent enough to be used for formulas, and if so what the conversion rate of ticks to an actual unit of time is.
No promises but if I figure it out ill tag you on it.
-
Paul Newcome ✭✭✭✭✭✭
@[email protected]Thanks for the feedback. I have a few tests running too, but I just started them yesterday afternoon. I'll keep an eye on mine too.
thinkspi.com
-
Genevieve P. Employee Admin@Paul NewcomeThank you so much! This is agreatresource and I'm very excited to have so many time-based solutions in one place.
-
Paul Newcome ✭✭✭✭✭✭
@Genevieve PI figured this would help in a few different ways. I also have a few others I am working on getting put in here in addition to the one that@[email protected]and I are testing.
thinkspi.com
-
Hi Paul! you are awesome btw!
I have a problem and hope you have a solution.
My project duration days are weekdays only so I set my project settings as such. However, I do have a few tasks that only occur on weekends and my sheet is not calculating that in based on my settings. Do you have a formula that will deliver a date based on a predecessor that is a weekend and ignores the weekday? I hope that made sense.
Kay!
-
Paul Newcome ✭✭✭✭✭✭
@Kerlyn MottersheadI moved your question to a separate threadHERE.
thinkspi.com
-
Debbie J ✭✭✭✭✭Thank you I will review and see if this answered my question on the other feed.
-
Debbie J ✭✭✭✭✭
Paul what would be the formula to calculate business hours
-
Jim Rood ✭✭✭✭
Thank you so much for creating this resource. I had all been given up hope on being able to use Smartsheet for a time sheet approval use case.
-
Paul Newcome ✭✭✭✭✭✭
@Debbie JCould you start a new thread and "@mention" me in it with more details?
Are you using 12 hour or 24 hour format? If 12 hour, are "am" and "pm" in the same cell as the time or in a separate one? Will there be multiple dates involved in a single calculation? What exactly are your business hours? If calculating business hours, do you also need to account for breaks? Screenshots with sensitive/confidential data removed, blocked and/or replaced with "dummy data" as needed showing column names so that the layout is clear would also be very helpful.
@Jim RoodGlad this thread was able to help you. If you can't find exactly what you are looking for, please see my comment/questions above.
thinkspi.com
-
Jim Rood ✭✭✭✭
@Paul NewcomeRather than start a new thread immediately, I did a search and found something similar to the functionality I'm trying to create. Link to thread:https://community.smartsheet.com/discussion/comment/256455#Comment_256455
Essentially, I'm trying to use the detail edit of DynamicView to capture the submit date/time for 4 entries daily (Time In, Lunch Out, Lunch In, Time Out), in essence a "SmartClock." The solution provided in that threat was to create a data entry table and then use it to populate a daily record. It isn't as efficient as having a system date/time stamp that could be applied as needed.
If you have any additional thoughts or comments, I'd love to hear your perspective.
-
Paul Newcome ✭✭✭✭✭✭
I personally would use two separate forms. One on the time sheet for the initial clocking in and then another on a separate sheet where users could check a box to designate which "punch" they are logging.
The first form on the time sheet creates that day's row for the user and then formulas with cross sheet references can be used to pull the remaining "punches" from the second sheet.
thinkspi.com
-
Paul Newcome ✭✭✭✭✭✭
Here is another time based solution as a result of@Debbie J's above request regarding business hours.
We needed to calculate how many WORKING hours were in between two dates/times.
Times are in 24 hour format with a colon between hours and minutes.
Weekends, holidays, and non-working hours all needed to be excluded.
Holiday dates are listed in a column on a separate sheet.
Working hours are Mon-Fri, 8am - 5pm.
thinkspi.com
-
Paul Newcome ✭✭✭✭✭✭
Found another one that I have used a lot but don't seem to have published:
在处理时间时我发现它更容易convert the times into 24hr format. To pull the hour from a system generated column such as Created or Modified, you can use this:
=VALUE(MID([email protected], FIND(" ",[email protected]) + 1, FIND(":",[email protected]) - (FIND(" ",[email protected]) + 1))) + IF(VALUE(MID([email protected], FIND(" ",[email protected]) + 1, FIND(":",[email protected]) - (FIND(" ",[email protected]) + 1))) <> 12, IF(FIND("P",[email protected]) > 0, 12), IF(FIND("A",[email protected]) > 0, -12))
thinkspi.com
