Taking 3 dates and solving for a yes no value

I appreciate the help from my last question, hope this one will be just as simple for you excel guru's.

My issue this time is I want them to input 3 dates.

A1 DATE1
B1 DATE2
C1 DATE3

Need to figure out if DATE1 is within 120 days before or after DATE3 AND also must be prior to or same as DATE2 to say yes

if DATE1is not within 120 days from DATE3 OR its not before DATE2 will say no.

I may have my formatting wrong on my DATE lines, i have them manually input the date like this = 01/20/2010

It would be nice if i could have them just type in 012010 and it will add the / for them and or extend the 10 to 2010, but my main concern is the formula first and formost. if they have to enter it themselves thats fine.

My concern is that with a formula i got yesterday, i don't think its calculating the dates when i go back past a year, cause from todays date 120 days would go back to december of 09..and i was gettin mixed results. Any light on the subject would be appreciated. Or if i need to re-explain this situation, plz ask.

Sorry for the weird request.

any takers? thanks in advance.

Better to enter the actual date..., if not, you would have to have the result dates formatted as you want in other cells.. or use VBA to convert...

Try:

=IF(AND(ABS(C1-A1)<=120,A1<B1),"YES","NO")

The thing to know abou dates is that they are just numbers. 1 is 1Jan1900, 2 is 2nd Jan 1900 etc today is number 40282.


=if(and(abs(date1-date3)<=120),date1<date2),"yes","no")

Try:


=IF(COUNT(A1:C1)<>3,"",IF(AND(ABS(C1-A1)<=120,A1<B1),"YES","NO"))

The difference between the G13 and G16 dates is actually 121 days...

Originally Posted by Argile79

yeah, hah as soon as i sent that i recalculated it, so that should be no...now it seems to be working, for a while it was keeping yes forever and then when it hit no would be no forever, even when i was in yes's..but now it seems to be good..so my bad..thanks so much, i sent it to a hand full of people to see if we can break it..but til then, you guys rock..thanks alot. Ill keep you posted.

Possibly you have your Excel set to not do Auto recalcs?

Go to Tools|Options and in the Calculation tab.. check Automatic in the Calculation area.

As I initially said, not in the same cell.. at least I don't think so. You would have to apply VBA event macro... (I am not sure exactly how).. or you would have another cell contain the result of your formatted date based on those inputs....

The macro should actually convert your input to a real date (not just make it look like one) so the formulas would work... or else, the formulas would have to be altered to accomodate.

My suggestion would be to start a new thread in the programming forum specifically asking for a macro to do this conversion in the same cell as entered automatically. I am sure you will get a relatively quick reply

Here might be a macro that may help:

http://www.cpearson.com/excel/DateTimeEntry.htm