| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore blank cells when calculating date for If, Then function
I have a training spreadsheet that uses the date function "Today" along with
the date training was completed to calculate when training is overdue. Is there a way to have Excel ignore blank cells. Right now it is counting a blank cell as "Overdue". My formula is =IF(($C$3-J8)1095, "OVERDUE", " "), where C3 = TODAY(); J8 is the date of the last training class and 1095 is the amount of days it would be considered "overdue". Thanks, Andrea |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore blank cells when calculating date for If, Then function
Try
=IF(AND(ISNUMBER(J8),C3-J81095),"Overdue","") Mike "AndreaS13" wrote: I have a training spreadsheet that uses the date function "Today" along with the date training was completed to calculate when training is overdue. Is there a way to have Excel ignore blank cells. Right now it is counting a blank cell as "Overdue". My formula is =IF(($C$3-J8)1095, "OVERDUE", " "), where C3 = TODAY(); J8 is the date of the last training class and 1095 is the amount of days it would be considered "overdue". Thanks, Andrea |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore blank cells when calculating date for If, Then function
That worked perfectly--thank you so much!
Andrea "Mike H" wrote: Try =IF(AND(ISNUMBER(J8),C3-J81095),"Overdue","") Mike "AndreaS13" wrote: I have a training spreadsheet that uses the date function "Today" along with the date training was completed to calculate when training is overdue. Is there a way to have Excel ignore blank cells. Right now it is counting a blank cell as "Overdue". My formula is =IF(($C$3-J8)1095, "OVERDUE", " "), where C3 = TODAY(); J8 is the date of the last training class and 1095 is the amount of days it would be considered "overdue". Thanks, Andrea |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ignore blank cells when calculating date for If, Then function
First of all change the space " " to a blank ""
=IF(OR(J8="",$C$3-J8<=1095),"","OVERDUE") or better =IF(OR(J8="",TODAY()-J8<=1095),"","OVERDUE") that way you don't need an extra cell -- Regards, Peo Sjoblom "AndreaS13" wrote in message ... I have a training spreadsheet that uses the date function "Today" along with the date training was completed to calculate when training is overdue. Is there a way to have Excel ignore blank cells. Right now it is counting a blank cell as "Overdue". My formula is =IF(($C$3-J8)1095, "OVERDUE", " "), where C3 = TODAY(); J8 is the date of the last training class and 1095 is the amount of days it would be considered "overdue". Thanks, Andrea |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore blank cells | Excel Discussion (Misc queries) | |||
| calculating date/time with blank cells | Excel Discussion (Misc queries) | |||
| can you tell excel to ignore #### cells when calculating | Excel Worksheet Functions | |||
| ignore blank cells in function | Excel Discussion (Misc queries) | |||
| How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
Hybrid Mode
