How to calculate time off within business hours excluding weekends

daonnen

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)

Toufong Vang

@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

daonnen

I have tried adding Networkdays to my formula but no matter where I put it I get the Unparseable error

daonnen

https://community.smartsheet.com/discussion/comment/414372#Comment_414372

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.

Toufong Vang

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

daonnen

https://community.smartsheet.com/discussion/comment/414413#Comment_414413

Okay so I got it to stop doing the unparseable error but now it is calculating completely wrong

Toufong Vang

@daonnen Hmm... What part is not working as expected?

daonnen

https://community.smartsheet.com/discussion/comment/414525#Comment_414525

=((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)

Toufong Vang

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)

Kiley Vander Wyst

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?

Toufong Vang

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.