If formula for date range
 

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.


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=374135

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=374135

swatsp0p Wrote:
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
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=374135

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)


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=374135

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)


--
PokerZan
------------------------------------------------------------------------
PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480
View this thread: http://www.excelforum.com/showthread...hreadid=374135

PokerZan Wrote:
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))


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=374135

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
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374135

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374135

Thanks mangesh_yadav the formula worked perfectly


--
rediproof
------------------------------------------------------------------------
rediproof's Profile: http://www.excelforum.com/member.php...o&userid=23714
View this thread: http://www.excelforum.com/showthread...hreadid=374135

Hi rediproof

Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374135