Forum Discussion

Angus239's avatar
Angus239
Copper Contributor
Mar 13, 2024
Solved

Calculating amount of hours worked within certain shift hours

Hi Tech Community,   I'm in the process of putting together an excel document for calculating worked hours for some employees and i've run into an issue with the employees starting after the design...
excel
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Mar 13, 2024

    Angus239 
    hello,
    Assuming that your Start(A2) and End(B2) has included a date

     

    you can use these following formulas to achieve your goal:

    Total Hours

     

    =ABS(24*((MIN(TIME(6,0,0),MOD(B2-TIME(18,0,0),1))-MIN(TIME(6,0,0),MOD(A2-TIME(18,0,0),1)))+(MAX(TIME(6,0,0),MOD(B2-TIME(18,0,0),1))-MAX(TIME(6,0,0),MOD(A2-TIME(18,0,0),1)))))

     

    Day Shift Hours

     

    =IF(B2>=$J$2,HOUR(B2)-6+MINUTE(B2)/60,0)

     

    Night Shift Hours

     

    =F2-D2

     

     

    OVERVIEW:

    I've attached a sample file for you

Angus239's avatar
Angus239
Copper Contributor
Mar 13, 2024
I've been able to get the total amount of hours worked with the following formula:

=24*((MIN(TIME(6,0,0),MOD(C10-TIME(18,0,0),1))-MIN(TIME(6,0,0),MOD(B10-TIME(18,0,0),1)))+(MAX(TIME(6,0,0),MOD(C10-TIME(18,0,0),1))-MAX(TIME(6,0,0),MOD(B10-TIME(18,0,0),1))))

Now I just need to figure out how to change it to fit the requirements for the shift times if anyone had any pointers going forward : )

Resources