Conditional formatting based on date range

[Guest]

Hi,

I am trying to create a formula which will;

Firstly check if a date is in a range:

* If not, then zero should be returned

*If yes - the formula should check again to see if the date matches a
specific date (only needs to match the month - not the exact day!)

(If the date does match- Then formula should then return a value from
another table.

If it doesn't match the exact date, (but is still part of the original
range) then a different $ value should be calculated based. This is based on
a total $ value divided by a number of specified months.

I need to work this out for many years information!

So far I have;

=IF(AND(F$1>=data!$K2, F$1<=data!$H2),
(IF(G$1=data!K2),D2,(data!$I2/$C2),(""))

Which doesn’t work - Anybody got any ideas?

Many Thanks

 

[Guest]

Could you post some example with numbers? The sintaxis of the formula is not
wrong at first sight, so what do you mean by "doesn't work"?
What puzzles me is why you are using both F1 and G1 in the comprobations,
maybe with data it will be more clear (if that is the month comprobation,
maybe changing G$1=data!K2 for MONTH(F$1)=MONTH(data!$K2) can help)

Miguel.

 

[Guest]

Hi

Try this:
=IF(AND(F$1>=data!$K2, F$1<=data!$H2), IF(G$1=data!K2,D2,data!$I2/$C2),"")

Andy.

 

[David Biddulph]

Hi,

I am trying to create a formula which will;

Firstly check if a date is in a range:

* If not, then zero should be returned

*If yes - the formula should check again to see if the date matches a
specific date (only needs to match the month - not the exact day!)

(If the date does match- Then formula should then return a value from
another table.

If it doesn't match the exact date, (but is still part of the original
range) then a different $ value should be calculated based. This is based
on
a total $ value divided by a number of specified months.

I need to work this out for many years information!

So far I have;

=IF(AND(F$1>=data!$K2, F$1<=data!$H2),
(IF(G$1=data!K2),D2,(data!$I2/$C2),(""))

Which doesn't work - Anybody got any ideas?

1 If the value you are trying to test is in F1, you've used that in one
place but you've used G1 in the next line. Or is G1 something you've
created to deal with only needing the same month?

2 You've used K2 as one limit for the range of dates, and you've also
used it as the reference against which to check for the specific date; was
that intentional?
[But again it isn't clear how you've dealt with the fact that you're only
looking for the same month. You may want to use the MONTH() function, but
perhaps you also want to check for it being the same YEAR()?]

3 The syntax of your second IF statement is illegal. Within the brackets
you need the condition, then a comma then the result if true, then another
comma and the result if false. You've closed the bracket after the
condition. You don't need the bracket before the second IF, and you've also
got additional sets of brackets which aren't necessary & which merely
confuse you when you're looking to see what's where, so perhaps that second
line should read:
IF(G$1=data!K2,D2,data!$I2/$C2),"")

4 You said the answer if it didn't fall within the first range should be
zero, but you've given "" to return an empty result.