Calculating amount of hours worked within certain shift hours

[Angus239]

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-... 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!

[#2]

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. 

[#3]

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

[#4 · Accepted solution]

@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

[#5]

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?

[#6]

@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)

[#7]

@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.