Need help to simplify a nested formula

catherinemoore

New Member
Joined
Oct 10, 2006
Messages
10
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
 

Some videos you may like

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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Oct 10, 2006
Messages
10
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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Aug 23, 2006
Messages
132

ADVERTISEMENT

=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, :rolleyes:
 

catherinemoore

New Member
Joined
Oct 10, 2006
Messages
10
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
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Oct 10, 2006
Messages
10
No, it doesn't work - it gives the result "N" whatever you put in. Any other ideas?

Thanks

Catherine
 

Watch MrExcel Video

Forum statistics

Threads
1,113,918
Messages
5,545,025
Members
410,647
Latest member
bernardazar
Top