Formulas for Calculating Time

Paul Newcome

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)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

Paul Newcome

@Genevieve P Here's that Time post we were talking about before.

@L@123 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 Wilday Since 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.

L_123

Hey @Paul Newcome

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

@L@123 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.

Genevieve P.

@Paul Newcome Thank you so much! This is a great resource and I'm very excited to have so many time-based solutions in one place.

Paul Newcome

@Genevieve P I 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 @L@123 and I are testing.

Kerlyn Mottershead

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 Mottershead I moved your question to a separate thread HERE.

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 J Could 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 Rood Glad this thread was able to help you. If you can't find exactly what you are looking for, please see my comment/questions above.

Jim Rood

@Paul Newcome Rather 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.

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.

ORIGINAL POST

PUBLISHED SOLUTION

Paul Newcome

Found another one that I have used a lot but don't seem to have published:

When working with time I find it is easier to 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(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) <> 12, IF(FIND("P", Created@row) > 0, 12), IF(FIND("A", Created@row) > 0, -12))