# Need help to simplify a nested formula

#### catherinemoore

##### New Member
I need help with the formula in F4. In row 4 the working hours are entered for the day. In row 5, any callout hours are added. The aim is to show "N" in F4 if there is less than a 12 hour gap between each finish time and the next start time, or show "Y" if there are 12 hours or more.

I need to include the following in the formula to get an "N" result:
(hrs between D4 & C5)>12
(hrs between D4 & G4)>12
(hrs between D4 & G5)>12
(hrs between D5 & G4)>12
(hrs between D5 & G5)>12
(hrs between G4 & G5)>12

So far I have included the first two criteria in the formula, but it's already pretty lengthy, so if anyone has any ideas on how to simplify it, I would really appreciate it!
HOURS.xls
CDEFGHIJ
2SUNDAYRESTMONDAYREST
3STARTFINISHHRS13/14STARTFINISHHRS13/14
408:0016:008.0N08:0016:008.0
522:0023:001.00.0
Worked Hrs

Thanks.

Catherine

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### West Man

##### Well-known Member
Quick and untested but try =IF(MAX(C5-D4,1-D4+G4,1-D4+G5,1-D5+G4,1-D5+G5,G5-G4)>0.5,"N","Y")

This may at least point you in the right direction.

#### catherinemoore

##### New Member
I can see where you are coming from, but it doesn't quite work. I like the MAX idea, but still can't get the calcs inside the MAX function to work. Does anyone have any ideas?

#### West Man

##### Well-known Member
After posting, I did a test on your sample data and got the proper result. Under what circumstances does the formula fail?

#### JamieDuncan

##### Board Regular

=IF((D4+C5)>12,(IF((D4+G4)>12,(IF((D4+G5)>12,(IF((D5+G4)>12,(IF((D5+G5)>12,(IF((G4+G5)>12,"N","Y")),"Y")),"Y")),"Y")),"Y")),"Y")

this ones a little bit shorter,

#### catherinemoore

##### New Member
West Man

Sorry - I got a little confused. I actually need to know if the smallest interval is less than 12 hrs, so the formula would now look like this:

=IF(MIN(C5-D4,1-D4+G4,1-D4+G5,1-D5+G4,1-D5+G5,G5-H4)<0.5,"N","Y")

The problem is that when some of the cells are blank, the calculation produces a negative number, which then ends up being the minimum and affects the result. How can I get the formula to ignore the calculations which involve a blank cell (e.g. if C5-D4 produced a negative number, then it would be ignored in the calculation of the minimum)?

Thanks

Catherine

#### West Man

##### Well-known Member

I am bumping this in hope that others can assist you. I got hit with an emergency that will ty me up for a couple days. Sorry I can not help further at this time.

#### West Man

##### Well-known Member
Untested but try:

=IF(MIN(IF(C5-D4<0,9999999,C5-D49),IF(1-D4+G4<0,9999999,1-D4+G4),IF(1-D4+G5<0,9999999,1-D4+G5),IF(1-D5+G4<0,9999999,1-D5+G4),IF(1-D5+G5<0,9999999,1-D5+G5),IF(G5-H4<0,9999999,G5-H4))<0.5,"N","Y")

I'm sure a shoter array formula is possible.

#### catherinemoore

##### New Member
No, it doesn't work - it gives the result "N" whatever you put in. Any other ideas?

Thanks

Catherine

#### catherinemoore

##### New Member
Can anyone help? I'm still stuck on this!

Thanks!

Replies
2
Views
78
Replies
1
Views
47
Replies
4
Views
95
Replies
6
Views
78
Replies
6
Views
127