Mr Whippy
|
| posted on 25/10/12 at 10:05 AM |
|
|
Formula Help with excel, late night can't think :o(
Hi bad thing to sort out just now, I don't feel well 
Spent about an hour on this and so fed up, not helped by noisy folk strimming grass outside the office  wtf it's nearly november!
All i'm trying to do is get the cell value to be true and false if the date falls between the week above so the conditional format changes the
colour. Tried many versions of the formula and none work correctly
There's some free on the web but IT have blocked any uploads.
The end date is just the start date + the duration
Can anyone help, please 
[Edited on 25/10/12 by Mr Whippy]
|
|
|
|
|
MRLuke
|
| posted on 25/10/12 at 10:17 AM |
|
|
Give me a sec and ill have a look for you
|
|
|
Mr Whippy
|
| posted on 25/10/12 at 10:22 AM |
|
|
quote:
Originally posted by MRLuke
Give me a sec and ill have a look for you
thanks, still not getting it working aaaahhhh!
|
|
|
Slimy38
|
| posted on 25/10/12 at 10:30 AM |
|
|
Your formula is wrong isn't it? In English, yours is;
If the start date is greater than the test date AND test date is less than end date
Shouldn't it be;
If test date is greater than start date and test date is less than end date
or;
=IF(AND(N$2>=$G4, N$2<=$H4),TRUE,FALSE)
|
|
|
MRLuke
|
| posted on 25/10/12 at 10:32 AM |
|
|
This appears to work:
=IF($G4>N$2,FALSE,IF($H4>N$2,TRUE,FALSE))
I think it can probably be simplified. As above lol, both work the same.
[Edited on 25/10/12 by MRLuke]
|
|
|
rodgling
|
| posted on 25/10/12 at 10:32 AM |
|
|
You've got IF(condition, true, false)... lose the IF, it does nothing. Just have AND(N$2>=$G4, N$2<=$H4) (or whatever it needs to be).
|
|
|
Mr Whippy
|
| posted on 25/10/12 at 10:35 AM |
|
|
quote:
Originally posted by MRLuke
This appears to work:
=IF($G4>N$2,FALSE,IF($H4>N$2,TRUE,FALSE))
I think it can probably be simplified. As above lol, both work the same.
[Edited on 25/10/12 by MRLuke]
yeah it works! Well done  what a difference a fresh mind makes or one less hung over
Thank you very much that was a big help
|
|
|
