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

Hmmm when trying to upload the excel document itself the message form says the xlsx file format is not supported, hopefully this is enough to show what i'm referencing. 

 

Resources