IF Statement with Date data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I run Win2k with Excel 2K

I would like to have a formula that looks at a range of dates, then
determines the "latest" date in that range. Then if that "latest" date is
greater than 30 days from the "current" date, display a message "DUE".

If your require anymore info please let me know.

Thanks

John
 
Assuming dates are running down in col A
Then in say, B1:=IF(MAX(A:A)<TODAY()-30,"due","")
Adapt the range to suit
 
Dates* are just numbers in Excel, so the latest date within a dates range
will be the highest number (ie maximum) within that range.
*real dates recognized by Excel, that is

---
 
John Calder said:
If the dates are across columns how would the formula look then?
Click to expand...

Assuming dates are running across in row 2, from B2 across
Then in say, A1: =IF(MAX(2:2)<TODAY()-30,"DUE","")
Adapt the range to suit

---
 
Thanks again Max

It appears to work ok except for one thing. When I delete all the data from
the range the "DUE" still shows?

Is there a way I can have the formula cell return to blank when the data has
been deleted?

Thanks
 
Thanks again Max
Click to expand...

You're welcome, John
It appears to work ok except for one thing. When I delete all the data
from
the range the "DUE" still shows?
Is there a way I can have the formula cell return to blank when the data
has
been deleted?
Click to expand...

Just use an IF trap in front, eg in A1:

=IF(MAX(2:2)=0,"",IF(MAX(2:2)<TODAY()-30,"DUE",""))


---
 
Back
Top