SOLVED

Help with calculating empty Time data in cells

Copper Contributor
I am attempting to create a Roster Spreadsheet to calculate hours workedin a week (the hours worked are calculated in a hidden field), The tempolate must cater for days off , i.e. no times entered, but the formulae needs to be able to cater for this or else #NUM appears
 18  19  20  21 
 Sun  Mon  Tues  Wed 
Hours Worked Start End Start End Start End Start End
#NUM!   20:00 8:00 8:00 20:00  
 #NUM!  11:30  11:30   
The daily hours worked are totaled in the hours worked column.
        
A second issue I have is when the hours worked is < 7 hours , then the lunch break is not deducted.
18 Replies
best response confirmed by joem1964 (Copper Contributor)
Solution

@joem1964 

In H7:

 

=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*$F6/1440,1/1440))

Thanks Hans, this has worked perfectly and I will now amend the spreadsheet to reflect the new formula, Many thanks again
Hello Hans, Many thanks for your help and this resolved the issue for me, however, I now need to also cater for night shifts, apparently they work 12 hour shifts without the deduction of Breaks, can that be incorporated in the same formula?
(Night shifts typically atart at 19:45 until 07: 45 or 20:00 to 08:00)

Joe

@joem1964 

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

Hello Hans,

Yes, I have tested with various times and it works perfectly, thank you very much for your halp again, Joe

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

@Kuri_91 

What defines a night shift?

@Hans Vogelaar 

 

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

@Kuri_91 

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

@Hans Vogelaar 

 

https://docs.google.com/spreadsheets/d/1uZxv0JqEfS0i4R4IjoWdTIJyEY8h4m_u/edit?usp=sharing&ouid=10446... 

 

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.

 

 

 

 

 

@Kuri_91 

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

See the attached version.

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 

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?

@Hans Vogelaar 

 

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 

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

@Hans Vogelaar 

 

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

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

@Hans Vogelaar 

 

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

 

It works like a magic

 

Many Thanks

Kuri

1 best response

Accepted Solutions
best response confirmed by joem1964 (Copper Contributor)
Solution

@joem1964 

In H7:

 

=IF(OR(H6="",I6=""),0,MROUND(MOD(I6-H6,1)-(MOD(I6-H6,1)>=7/24)*$F6/1440,1/1440))

View solution in original post