Blank field formatted as date
I am trying to use a function to evaluate when a course is due. In column D
there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. |
Try this
=IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ") -- HTH Bob Phillips "Emily8" wrote in message ... I am trying to use a function to evaluate when a course is due. In column D there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. |
On Tue, 11 Oct 2005 08:46:34 -0700, Emily8
wrote: I am trying to use a function to evaluate when a course is due. In column D there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY()," due"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. You could do something like: =IF(ISNUMBER(D3),IF(DATE(YEAR(D3)+3,MONTH(D3),DAY( D3))<=TODAY(),"due",""),"") By the way, I would advise returning NULL strings rather than, as in your formula, a <space character. --ron |
=if(d3="","",yourformulahere)
But any date 3 years in the future will never be <= today(). I'm not sure what that formula should be. =if(d3="","",date(year(d3)+3,month(d3),day(d3))) ??? Emily8 wrote: I am trying to use a function to evaluate when a course is due. In column D there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. -- Dave Peterson |
Thanks Bob, Ron and Dave. It works perfectly with the AND, and I've altered
it to be a null string. Dave, the admin types in the date the course was taken and then if it is longer than 3 years ago, it comes up with "due". Not very elegant, but I just needed to make the formula work! My users weren't very interested in alternative methods to track this. Thanks, All! "Bob Phillips" wrote: Try this =IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ") -- HTH Bob Phillips "Emily8" wrote in message ... I am trying to use a function to evaluate when a course is due. In column D there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. |
Ooh. I see. I was (incorrectly) expecting the date in D3 to be more current.
That was a bad assumption! Emily8 wrote: Thanks Bob, Ron and Dave. It works perfectly with the AND, and I've altered it to be a null string. Dave, the admin types in the date the course was taken and then if it is longer than 3 years ago, it comes up with "due". Not very elegant, but I just needed to make the formula work! My users weren't very interested in alternative methods to track this. Thanks, All! "Bob Phillips" wrote: Try this =IF(AND(D3<"",DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))< =TODAY()),"due"," ") -- HTH Bob Phillips "Emily8" wrote in message ... I am trying to use a function to evaluate when a course is due. In column D there may or may not be a date listed. I'm using this formula in to evaluate if the course is due to be renewed. =IF(DATE(YEAR(D3)+3,MONTH(D3),DAY(D3))<=TODAY(),"d ue"," ") The problem arises when D3 is blank. It is still evaluating the blank cell as a date, 01/01/1907 and returning "due" because it is less than today. If D3 is blank I need the formula to return an empty string, which I thought the above did. -- Dave Peterson |
All times are GMT +1. The time now is 09:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com