If formula for date range

[rediproof]

I am trying to set up a formula to check milestones which fall between 15 days + or - of todays date using IF. If the result matches I want the true value to be text in another cell.

I've come up with the formula to check if it matches today (see below) but am having problems when trying to add in the +/- 15 days.

=IF(C17=B26,B17,0)

C17 is milestone date
B26 is todays date
B17 is milestone description

I also want to extend this to multiple cells.

Any help would be appreciated.

[swatsp0p]

Use this formula in your desired output cell:

=IF(AND(C17<=B26+15,C17>=B26-15),B17,"Beyond 15 days")

Dates within +/- 15 days of the date in B26 will return the value in B17, dates earlier or later than 15 days from that date will return "Beyond 15 days" -- you can edit this output to meet your needs

HTH

Bruce

[rediproof]

Use this formula in your desired output cell:

=IF(AND(C17<=B26+15,C17>=B26-15),B17,"Beyond 15 days")

Dates within +/- 15 days of the date in B26 will return the value in B17, dates earlier or later than 15 days from that date will return "Beyond 15 days" -- you can edit this output to meet your needs

HTH

Bruce

Thanks Bruce!!!!

[rediproof]

If I wanted to extend this formula to other cells on the same row such as e&f how would this be done? I've tried the formula below but get a #value error message

=IF(AND(C17<=$B$26+15,C17>=$B$26-15),B17,0),IF(AND(F17<=$B$26+15,F17>=$B$26-15),E17,0)

[PokerZan]

Does taking the absolute ($) referece off of the string help?

Like this:

=IF(AND(C17<=B26+15,C17>=B26-15),B17,0),IF(AND(F17<=B26+15,F17>=B26-15),E17,0)

[rediproof]

Does taking the absolute ($) referece off of the string help?

Like this:

=IF(AND(C17<=B26+15,C17>=B26-15),B17,0),IF(AND(F17<=B26+15,F17>=B26-15),E17,0)

B26 is a cell that contains todays date so it should not cause a problem. I've tried this formula and it does partly work however, for the second set of cells it gives the false answer even when the data is true and I don't know how to get a space to appear between the 2 values.

=IF(AND(C17<=$B$26+15,C17>=$B$26-15),B17,0)&(IF(AND(F17<=$B$26+15,F17>=$B$26-15),E17,0))

[mangesh_yadav]

Hi rediproof,

You want the whole result in one cell only, or are you talking about 2 such results. You can break up your formula into 2 cells as

=IF(AND(C17<=$B$26+15,C17>=$B$26-15),B17,0)

=IF(AND(F17<=$B$26+15,F17>=$B$26-15),E17,0)

If you want to check both conditions in one cell then you need to do something like:

=IF(AND(C17<=$B$26+15,C17>=$B$26-15),B17,0) & " " & IF(AND(F17<=$B$26+15,F17>=$B$26-15),E17,0)

This should come on one line only

Mangesh

[mangesh_yadav]

I've tried this formula and it does partly work however, for the second set of cells it gives the false answer even when the data is true

Also can you give your sample data for which you say that you don't get your expected answer..

Mangesh

[rediproof]

Thanks mangesh_yadav the formula worked perfectly

[mangesh_yadav]

Hi rediproof

Thanks for the feedback.

Mangesh