Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Date evaluation
I want another cell to evaluate if there is a date (of any kind) in another
cell. If there is then I want the formula to place the date minus 30 days. If there's not, then I want it to say "NA". Ex: Cell A4 can have, "OK", "Overdue", or physically input a date. Cell A5 will be A4-30, if there is a date. If there is not then A5 will read "NA". Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Date evaluation
First install this UDF:
Function issdate(r As Range) As Boolean issdate = IsDate(r.Value) End Function and then: =IF(issdate(A4),A4+30,"NA") -- Gary''s Student - gsnu200796 "bigbird98" wrote: I want another cell to evaluate if there is a date (of any kind) in another cell. If there is then I want the formula to place the date minus 30 days. If there's not, then I want it to say "NA". Ex: Cell A4 can have, "OK", "Overdue", or physically input a date. Cell A5 will be A4-30, if there is a date. If there is not then A5 will read "NA". Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Date evaluation
A date is just a number to Excel, so you could use this in A5:
=IF(ISNUMBER(A4),A4-30,"NA") However, you could put any number in A4 (like 10) to fool this. A date of 1st Jan 2008 has a serial value of 39448 (the number of elapsed days since 1st Jan 1990), so if your dates are meant to be quite recent you could also check for the value, eg: =IF(ISNUMBER(A4),IF(A439400,A4-30,"NA")) Hope this helps. Pete On Jul 23, 7:52*pm, bigbird98 wrote: I want another cell to evaluate if there is a date (of any kind) in another cell. *If there is then I want the formula to place the date minus 30 days. * If there's not, then I want it to say "NA". Ex: Cell A4 can have, "OK", "Overdue", or physically input a date. *Cell A5 will be A4-30, if there is a date. *If there is not then A5 will read "NA". Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Electronic Evaluation/Feedback Forms in Excel | Excel Discussion (Misc queries) | |||
Continuous Evaluation of Time Function NOW() ?? | Excel Worksheet Functions | |||
Vlookup evaluation | Excel Worksheet Functions | |||
Automatic evaluation of custom worksheet function | Excel Worksheet Functions | |||
Options Evaluation | Excel Worksheet Functions |