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 designated shift start time and finishing after the designated shift end time.

 

I've looked into https://techcommunity.microsoft.com/t5/excel/calculate-wage-by-hours/m-p/3274662 and https://techcommunity.microsoft.com/t5/excel/calculating-an-hourly-penalty-rate-within-a-shift-that-may-have/m-p/3266686#M139746 which were helpful and lead me to believe that I should be using the MOD function somehow but I can't seem to wrap my head around it.

 

I need to calculate how many day or night shift hours are worked across a clock in and clock out time frequently starting in the evening of one day and finishing during the day of the next.

 

The day shift hours are 06:00 AM to 06:00 PM and the night shift hours are 06:00 PM to 06:00 AM.

 

As an example, the employee starts at 07:00 PM and finishes at 08:30 AM so I would need to display that they've worked 11 night shift hours and 2.5 day shift hours.

 

I'll attach the extract sheet but any help would be greatly appreciated!

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

7 Replies

Sort By
  • Angus239's avatar
    Angus239
    Copper Contributor
    Mar 13, 2024
    Hi Rodrigo_,

    That works like a dream! I'll be sure to save this solution for later so I can learn from it!

    Thanks for helping out, would you recommend any resources so that i'd be able to learn how to use the functions that you used to solve my predicament?
    • Rodrigo_'s avatar
      Rodrigo_
      Steel Contributor
      Mar 13, 2024

      Angus239 
      AI is the most powerful tool now, you can have any ideas from it, for almost everything. But it depends on how you use them.

  • Rodrigo_'s avatar
    Rodrigo_
    Steel 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

    • Rodrigo_'s avatar
      Rodrigo_
      Steel 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)

       

  • 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 : )
  • 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