IF Statement with Date data
 

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

IF Statement with Date data
 

Assuming dates are running down in col A
Then in say, B1:=IF(MAX(A:A)<TODAY()-30,"due","")
Adapt the range to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John Calder" wrote:
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

IF Statement with Date data
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

IF Statement with Date data
 

Thanks Max, I will give it a go

John

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

IF Statement with Date data
 

Max

Thanks

If the dates are across columns how would the formula look then?

"Max" wrote:

Assuming dates are running down in col A
Then in say, B1:=IF(MAX(A:A)<TODAY()-30,"due","")
Adapt the range to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"John Calder" wrote:
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

IF Statement with Date data
 

"John Calder" wrote:
If the dates are across columns how would the formula look then?

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

IF Statement with Date data
 

Typo, line:
Assuming dates are running across in row 2, from B2 across

should read as:
Assuming dates are running across in row 2, from A2 across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

IF Statement with Date data
 

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


"Max" wrote:

Typo, line:
Assuming dates are running across in row 2, from B2 across

should read as:
Assuming dates are running across in row 2, from A2 across
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

IF Statement with Date data
 

"John Calder" wrote
Thanks again Max

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?

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---