| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unable to change date format on imported data | Excel Discussion (Misc queries) | |||
| compare date to column data | Excel Worksheet Functions | |||
| macro to sort data in worksheet by specific date | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| bringing data from one workbook to another using a date stamp func | Excel Worksheet Functions | |||
Linear Mode
