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

  • Toufong Vang
    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
    daonnen ✭✭✭

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

  • daonnen
    daonnen ✭✭✭

    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
    Toufong Vang ✭✭✭✭✭
    edited 01/19/24

    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
    daonnen ✭✭✭
    edited 01/19/24

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

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

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


  • daonnen
    daonnen ✭✭✭

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!