Help with calculating empty Time data in cells

HansVogelaar 

Really thankful for the solution, appreciate your patience and prompt responses.

It works like a magic

Many Thanks

Kuri

Kuri_91 

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

HansVogelaar 

Minimum 10 Hours worked for 60 minute break
Minimum 7 Hours worked for 30 minute break

Kuri_91 

So what is the minimum hours worked for a 60 minute break? 9.5, or 10, or 11.75, or ...?

HansVogelaar 

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

Kuri_91 

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?

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

Kuri_91 

You didn't use the formula that I posted in the highlighted cell.

See the attached version.

HansVogelaar 

https://docs.google.com/spreadsheets/d/1uZxv0JqEfS0i4R4IjoWdTIJyEY8h4m_u/edit?usp=sharing&ouid=104462821698118680185&rtpof=true&sd=true 

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.