SOLVED

New Contributor

# Help with calculating empty Time data in cells

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 (New Contributor)
Solution

# Re: Help with calculating empty Time data in cells

In H7:

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

# Re: Help with calculating empty Time data in cells

Thanks Hans, this has worked perfectly and I will now amend the spreadsheet to reflect the new formula, Many thanks again

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

Hello Hans,

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

What defines a night shift?

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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.

# Re: Help with calculating empty Time data in cells

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

See the attached version.

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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?

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

# Re: Help with calculating empty Time data in cells

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

It works like a magic

Many Thanks

Kuri