Apr 14 2021 04:33 AM
Apr 14 2021 04:42 AM
SolutionApr 14 2021 04:48 AM
Apr 22 2021 06:42 AM
Apr 22 2021 07:57 AM
Does this do what you want?
=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*(I6>H6)*$F6/1440,1/1440))
Apr 22 2021 08:03 AM
Jul 06 2022 11:19 AM - edited Jul 06 2022 11:21 AM
Hello Hans,
Many thanks for the formula, there are people working from 8 to 12 hr shift get 1 Hour break.
How do incorporate that into this formula?
Day -0 to 7Hrs = No Break
Day -7 - 8 hrs = 30mnts
Day 8 - 12 Hrs = 1hr Break
Night Shift = No Break
Using the same sheet as reference
Regards
Kuri
Jul 06 2022 12:46 PM
What defines a night shift?
Jul 06 2022 01:46 PM - edited Jul 06 2022 02:19 PM
Day Shift
8:00 am to 3pm - No break ( 7 Hours)
8:00am to 3:30pm - 30mnts Break ( More than 7 Hrs)
8:00 am to 5 pm - 30mnts Break ( 8 Hours)
8:00am to 8 pm - 1 hrs Break (12 Hours)
Night Shift
8:00pm to 8 am ( Night Shift) - No Break
=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*(I6>H6)*$F6/1440,1/1440))
Everything else is ok except the 12Hr shift, it is taking only 30mnts break.
thanks
Jul 06 2022 02:20 PM
In H7:
=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(I6>H6)*((MOD(I6-H6,1)>=7/24)+(MOD(I6-H6,1)>=8/24))/48,1/1440))
Jul 06 2022 03:40 PM
Thank you for your update
When I entered the new formula it got a little bit mixed up and give incorrect values for many shifts except the 8:00-20:00 shift and 20:00-8:00 shift
for Example 8:00 to 16:00 shift need to get a value of 7.30 hrs
I have attached the original file, the hours in blue column need to be 11:00hrs, instead of 11:30.
Rest of Hours Worked value in every other shift are ok with the old formula.
Kind Regards
Could please have a look at the attached file one more time.
Jul 07 2022 12:39 AM
You didn't use the formula that I posted in the highlighted cell.
See the attached version.
Jul 07 2022 02:34 AM - edited Jul 07 2022 03:02 AM
Thank you Hans for your time.
I did use the new formula..
But the problem I can't solve is as follows:-
Please have a look at the value of the Cell (G12) just above the blue cell. it is 8 Hrs
An employee working on a 8:00 to 17:00 hrs shift will get only 30 mnts of break.
So I have to bring that value to 8.30 to make his actual working hours as 8.30Hrs.
(When applying the new formula, we only get 8Hrs for payment.)
The Same time an employee working on 8:00 to 20:00 Hrs shift (12 hour Shift) get a 1Hr Break
So his actual working hours would be 11 Hrs. ( The new formula is perfectly OK there)
And people who work less than 7 Hours during day will also would not get any break, if they work from 8am to 2pm they may get paid for 6 Hours. ( New formula Ok here as well)
As well as Night shift (from 8:00pm to 8:00am) also would not get no break. They get paid for 12 Hrs. ( New formula OK here as well)
Shift Break Hours Worked
8:00 - 13:00. Nill. 5 Hrs
8:00 - 15:30 30 Mnts 7 Hrs
8:00 - 16:00 30 Mnts 7.5 hrs
8:00 - 16:45 30 Mnts 8.15 hrs
8:00 - 17:00 30 Mnts 8.5 Hrs
8:00 - 20:00 1 Hr 11 hrs
20:00- 8:00 Nill 12 Hrs ( Night Shift)
This is the all story...Hope you understand the solution I am looking for.
I need to accommodate all these conditions in a single formula.
Really sorry If I was not able to explain to you in earlier messages.
Thanks & Regards
Kuri
Jul 07 2022 03:26 AM
In your first post in this discussion, you wrote
Day -0 to 7Hrs = No Break
Day -7 - 8 hrs = 30mnts
Day 8 - 12 Hrs = 1hr Break
Night Shift = No Break
If I understand you correctly, that was not what you wanted?
Jul 07 2022 03:56 AM
I am really sorry, that was a mistake happened my side in a hurry.
These are the actual timings of the shifts people working on:-
Shift Hours Break Hours Worked
8:00 - 13:00. (5 hrs.) Nill. 5 Hrs
8:00 - 15:30 (7.30 hrs) 30 Mnts 7 Hrs
8:00 - 16:00 (8 hrs.) 30 Mnts 7.5 hrs
8:00 - 16:45 (8.4 5hrs.) 30 Mnts 8.15 hrs
8:00 - 17:00 (9 hrs.) 30 Mnts 8.5 Hrs
8:00 - 20:00 (12 hrs.) 60 Mnts 11 hrs
20:00- 8:00 (12 hrs.) Nill 12 Hrs ( Night Shift)
Once again really sorry for the confusion I have created.
Many thanks
Kuri
Jul 07 2022 04:07 AM - edited Jul 07 2022 04:08 AM
So what is the minimum hours worked for a 60 minute break? 9.5, or 10, or 11.75, or ...?
Jul 07 2022 05:45 AM
Minimum 10 Hours worked for 60 minute break
Minimum 7 Hours worked for 30 minute break
Jul 07 2022 05:46 AM
So the formula becomes
=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(I6>H6)*((MOD(I6-H6,1)>=7/24)+(MOD(I6-H6,1)>=10/24))/48,1/1440))
Jul 07 2022 06:10 AM
Really thankful for the solution, appreciate your patience and prompt responses.
It works like a magic
Many Thanks
Kuri
Apr 14 2021 04:42 AM
SolutionIn H7:
=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*$F6/1440,1/1440))