If formula for date range

  • Thread starter Thread starter rediproof
  • Start date Start date
R

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.
 
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 said:
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
Click to expand...

Thanks Bruce!!!!
 
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 said:
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)
Click to expand...

B26 is a cell that contains todays date so it should not cause
problem. I've tried this formula and it does partly work however, fo
the second set of cells it gives the false answer even when the data i
true and I don't know how to get a space to appear between the
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)
 
Hi rediproof,

You want the whole result in one cell only, or are you talking about
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 d
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

Manges
 
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
Click to expand...

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

Mangesh
 
Back
Top