Forum Discussion

nerdyplayer's avatar
nerdyplayer
Copper Contributor
May 20, 2024

Formula to calculate annual leave for longer shifts

Wanted to see if those smarter than me have an idea, and wanted to brainstorm.

 

i'm making a spreadsheet that can help users visualize how much leave they have in days & weeks.

IE 16 hours of annual leave, calculates 8h shift = 2 days.

weekly just divide by 40 hours.

 

issue comes in when co-workers who are doing 9h shifts. 

How it works in our organization

week 1: Mon-Thurs 9h shift. Friday 8h shift

week 2 :Mon-thurs 9h shift, Friday off.

This way in a pay period it is still 80 hours.

 

Trying to figure out a formula for calculating days. Part of me was thinking about doing elementary school math.

divide by 9, any remainder divide by 8, and add them together. ty

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 20, 2024

    nerdyplayer 

    To create a formula in Excel that calculates the number of days of annual leave for employees with varying shift lengths (9-hour and 8-hour shifts), you can indeed break it down as you described.

     Let's consider your scenario:

    • Week 1: Mon-Thurs 9h shifts, Fri 8h shift
    • Week 2: Mon-Thurs 9h shifts, Fri off
    • Over a two-week period, the total hours work out to 80 hours.

    The key here is to calculate the number of shifts that would make up the given hours of annual leave, taking into account the two different shift lengths.

    Here's a step-by-step approach to creating the formula:

    1. Calculate the number of 9-hour shifts.
    2. Calculate the remaining hours and convert them into 8-hour shifts if necessary.
    3. Sum the number of shifts.

    Formula:

    Assume the annual leave hours are in cell A1. The following formula calculates the total number of shifts:

    1. Calculate the number of 9-hour shifts: INT(A1 / 9)
    2. Calculate the remaining hours after 9-hour shifts: MOD(A1, 9)
    3. Calculate the number of 8-hour shifts from the remaining hours: IF(MOD(A1, 9) > 0, 1, 0)
    4. Sum these together to get the total number of days.

    Here's the combined formula:

    =INT(A1 / 9) + IF(MOD(A1, 9) > 0, 1, 0)

    This formula counts the number of 9-hour shifts and then checks if there are any remaining hours that could constitute an 8-hour shift.

    Example:

    If A1 contains 16 hours of annual leave:

    • INT(16 / 9) gives 1 (1 full 9-hour shift).
    • MOD(16, 9) gives 7 (remaining hours after one 9-hour shift).
    • IF(7 > 0, 1, 0) gives 1 (since 7 hours can be considered as one 8-hour shift).

    Thus, the formula =INT(16 / 9) + IF(MOD(16, 9) > 0, 1, 0) results in 1 + 1, which equals 2.

    For a more detailed formula that takes into account partial hours correctly, you can use:

    =INT(A1 / 9) + IF(AND(MOD(A1, 9) > 0, MOD(A1, 9) <= 8), 1, 0) + IF(MOD(A1, 9) > 8, MOD(A1, 9) - 8, 0)

    This formula ensures that:

    • It calculates the number of 9-hour shifts.
    • It adds one 8-hour shift if there are remaining hours up to 8.
    • It adjusts for any hours beyond the 8 hours from the remainder.

    Weekly Calculation:

    To convert hours into weeks considering your special weekly shifts:

    = A1 / 40

    This formula assumes a 40-hour work week.

    By using these formulas, you can create a more precise calculation for annual leave in terms of days and weeks, adjusting for the specific shift patterns in your organization. The text and the steps was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources