Can you calculate time in Smartsheet?
I am setting up a new sheet for when employees return to the office with staggered start times and duration of their lunch breaks. Is there a formula that would calculate their end time depending on what time they started? i.e., I entered 7:15 and take a 45 minute lunch, automatically fill in 4:00 for my end time that day. Another employee may start at 9 and take a 30 minute lunch.
I have looked at the formulas posted in the community and am not finding anything. I know Excel has a Time formula but I'm not seeing it in Smartsheet. If this isn't available, is there a work around until it would be available? Is a time function something that is being worked on?
Thank you,
Patty
Best Answer
-
HERE IS A PUBLISHED SHEET that shows the solution described below.
I used a helper column for this particular solution. It can be done in a single column, but that results in a bit of a monster formula that can be tough to troubleshoot if anything does happen to break or need tweaked.
I called the helper column End and the result is in the [End Time] column.
End:
=IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + (Lunch@row / 60) + 8
[End Time]:
=MOD(INT(End@row), 12) + ":" + IF((End@row - INT(End@row)) * 60 < 10, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "pm", "am")
Answers
-
There isn't a direct way to calculate time in Smartsheet yet, but I do believe it is being worked on.
In the meantime, there are a few different options for some workarounds.
How is the data being entered?
Are you using 12 or 24 hour time?
I see you entered colons in your post. Is that going to be consistent?
Would lunches always be set durations such as 30 or 45 minutes, or is it possible to take something different such as 32 or 48 minutes?
Is it always going to be the same amount of working hours (8) minus the lunch duration or do other numbers of working hours need to be accounted for?
Will it overlap midnight such as clocking in at 8pm, taking a 30 minute lunch, and clocking out at 4:30am?
I know it seems like a lot of questions (because it is), but I like to keep things as simple as possible. Some solutions require much more complexity than others, so getting all of the details worked out first allows us to cut out as much of that complexity as we can.
-
How is the data being entered? Manual entry by employee
Are you using 12 or 24 hour time? 12 hour time
I see you entered colons in your post. Is that going to be consistent? Yes
Would lunches always be set durations such as 30 or 45 minutes, or is it possible to take something different such as 32 or 48 minutes? They would always be 30, 45, or 60 minutes
Is it always going to be the same amount of working hours (8) minus the lunch duration or do other numbers of working hours need to be accounted for? Yes, working 8 hours per day
Will it overlap midnight such as clocking in at 8pm, taking a 30 minute lunch, and clocking out at 4:30am? No
-
Ok. Let me work something up, and I'll get back to you either with more questions or with a solution.
-
HERE IS A PUBLISHED SHEET that shows the solution described below.
I used a helper column for this particular solution. It can be done in a single column, but that results in a bit of a monster formula that can be tough to troubleshoot if anything does happen to break or need tweaked.
I called the helper column End and the result is in the [End Time] column.
End:
=IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, IF(CONTAINS("p", [Start Time]@row), 12), IF(CONTAINS("a", [Start Time]@row), -12)) + VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60) + (Lunch@row / 60) + 8
[End Time]:
=MOD(INT(End@row), 12) + ":" + IF((End@row - INT(End@row)) * 60 < 10, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "pm", "am")
-
This works perfectly. Thank
-
Excellent. Happy to help! 👍️
-
Dear Paul,
Is there any simple way to calculate hours between two time stamps? I am trying to make a sheet to calculate overtime.
my team would insert the time they went in and the time that went out and the hours would be calculated based on the In and out.
Thank you
Adrian
-
@Hadrian Mansueto Take a look in THIS THREAD at the solutions there and let me know if you can find something that works for you. There are solutions scattered about, but I think all of them should be copied over to page 3.
-
Hi, looking at the sheet you linked, the end times do not add up correctly. I'm trying to use this example, but stuck at that point.
-
For me it works well and helped me a lot.Thanks @Paul Newcome !!!
-
Hi @Paul Newcome - In reviewing the smartsheet you linked to, it appears that something isn't calculating correctly. For example, the first row is showing a 30 minute lunch starting at 12:00 pm ends at 8:30 pm. The formulas are more complex than my brain can handle, so I'm not sure how to play with it to try and adjust.
I also found this solution: https://community.smartsheet.com/discussion/76151/creating-an-end-time-based-on-an-amount-of-time-and-a-start-time
However, that one requires multiple helper fields and I liked the idea of only one extra field if that is possible.
I know it's been a long time since this was posted, and I appreciate any input/update!
-
@carriemcintyre That is correct though. If you start at noon, work for 8 hours, and include an additional 30 minute lunch break, that would put your end time at 8:30pm.
-
@Paul Newcome - oh duh! knew it was me. talk about cognitive bias, i was looking for a formula to calculate the number of minutes between two times without having to use 24 hour times, and so I read this sheet as calculating the end time of lunch.
-
@carriemcintyre There are a number of posts in the below thread that should help you out. It is easiest to calculate time differences when using 24 hour times, but you can use 12 hour times and then use an additional helper column to convert it to 24 hour times on the "back-end". This is also explained and shown throughout the thread.
-
Fantastic @Paul Newcome - thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!
