How to calculate time off within business hours excluding weekends
I have a leave sheet I am trying to create that calculates the hours a person is taking off that are only within business hours and excludes weekends and Holidays. I have the below formula that works to calculate hours elapsed between two times and dates, but I cannot figure out how to get it to only calculate week days and 8 hours working hours per day. Please assist.
=((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2)) / 60) + ([End Date]@row - [Start Date]@row) * 24) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2)) / 60)
Answers
-
@daonnen If your workdays are 8 hours, you can use NETWORKDAYS() to return the number of workdays between the Start Date and the End Date, and multiply that by 8 to get the number of Hours Taken. The function will exclude weekends.
In order to exclude holidays, you will need to have those dates listed somewhere. For example, say that your organization observes 12 holidays in 2024, and those dates are in the sheet, refHolidays, in the column 2024Holidays.
Your column formula in Hours Taken would be:
=NETWORKDAYS([Start Date]@row, [End Date]@row, {refHolidays 2024}) * 8
where {refHolidays 2024} is the cross sheet reference to the column, 2024Holidays, in the sheet, refHolidays.
Here's additional documentation on Smartsheet functions, https://help.smartsheet.com/functions.
More on cross sheet references, https://help.smartsheet.com/articles/2482644-create-cross-sheet-references
-
I have tried adding Networkdays to my formula but no matter where I put it I get the Unparseable error
-
Also I can't just use the Networkdays formula because I also need to calculate specific times in the same column. For example an employee took off 01/18/2024 from 8:00am-10:00am, they only took off two hours and just using the networkdays formula I get a value of 8 hours.
-
Also I can't just use the Networkdays formula because I also need to calculate specific times in the same column. For example an employee took off 01/18/2024 from 8:00am-10:00am, they only took off two hours and just using the networkdays formula I get a value of 8 hours.
@daonnen You'll still want to use the NETWORKDAYS() function--just subtract "1" from the expression's value (before multiplying it by 8), and then add the difference between [End Time]@row and [Start Time]@row. Whole days are 8 hours, and partial days are the difference between the end and start times.
((NETWORKDAYS() -1) * 8) + (difference between end and start times)
=((NETWORKDAYS([Start Date]@row, [End Date]@row, {refHolidays 2024}) - 1) * 8) + VALUE(LEFT([End Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2)) + (VALUE(RIGHT([End Time]@row,2)) - VALUE(RIGHT([Start Time]@row,2)))/60
-
Okay so I got it to stop doing the unparseable error but now it is calculating completely wrong
-
@daonnen Hmm... What part is not working as expected?
-
=((NETWORKDAYS([First Date of Leave]@row, [Last Date of Leave]@row) - 1) * 8) + ((VALUE(LEFT([End Time]@row, FIND(":", [End Time]@row) - 1)) + VALUE(RIGHT([End Time]@row, 2))) + ((([Last Date of Leave]@row - [First Date of Leave]@row) * 8)) - (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + VALUE(RIGHT([Start Time]@row, 2))))
Using this formula I am getting these time calculations. I have tried messing around with it but I don't know where I am going wrong. (I changed the name of the columns to reflect better what we need)
-
Hi, @daonnen , I can see a couple of errors.
1. You're mixing your time format: 12-hour and 24-hour. I your original post, you were using 24-hour format. My formula is based on that assumption.
2. It's unclear what the grayed part of your formula is attempting to do. In a 24-hour format, LEFT(hh:mm, 2) will return "hh. RIGHT(hh:mm, 2) will return "mm". These are two different units of measure--hours and minutes.
To get the difference between the "Start Time" and the "End Time" in hours:
(a) Find the difference between the hours;
VALUE(LEFT([End Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2))
(b) Find the difference between the minutes then divide by 60 to convert it to hours;
VALUE(RIGHT([End Time]@row,2)) - VALUE(RIGHT([Start Time]@row,2)))/60
(c) Add the two.
However, 17:00 - 08:00 = 9 hours. Since your workday is 8 hours, you'll want to wrap the part of the formula that calculates the difference between the Start and End time in the function, MIN() so that it will only return "8" or less. For example,
((NETWORKDAYS() -1) * 8) + MIN( (difference between end and start times) , 8)
=((NETWORKDAYS([Start Date]@row, [End Date]@row, {refHolidays 2024}) - 1) * 8) + MIN(VALUE(LEFT([End Time]@row, 2)) - VALUE(LEFT([Start Time]@row, 2)) + (VALUE(RIGHT([End Time]@row, 2)) - VALUE(RIGHT([Start Time]@row, 2))) / 60, 8)
-
Hi @daonnen
Thanks for you guidance on this function, your formula has helped me tremendously. I have one instance where I am getting a negative result. I think it is because of using the NETWORKDAYS function, and it defaulting to a 9 to 5 business hours and my time for this instance being outside of these "normal" business hours. See screenshot below.
Is there anyway around this?
-
Hi, @Kiley Vander Wyst , to track the time elapsed between "Created Date" and "Email Response", multiply the NETWORKDAYS() result by 24.
If you don't want to count the time outside of business hours, the beginning date and time in your formula cannot be the "created" timestamp when it is after 5:00 PM. You'll need to roll the start date and time to 9:00 AM the next business day.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!