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

Rodrigo_'s avatar
Rodrigo_
Iron Contributor
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

  • pini's avatar
    pini
    Copper Contributor
    Feb 02, 2025

    it dosnt work corect:

     

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Mar 13, 2024

    Angus239 
    I've mistaken on Day Shift Hours formula, I did not consider if the start(clock in) is below 6am.
    Here's the revision:

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

     

Resources