Date Comparison Driving Me Crazy

[Guest]

Hi - Im trying to compare dates using greater than (>) and less than (<)
operands in order to advance calculations. I am going to do a nested "IF"
statement (probably 3 or 4 levels) that will involve calculations. The
nested IF's will be comparing various dates (both dates in a cell, and "hard
coded dates"). I started with a simple 2-level nested IF returning only 0,
1, or 2, so I could confirm the logic worked before I put in the calcuating
formula's. They didn't work, and I noticed my "MM/DD/YY" date was not being
compared correctly in the cell. I think I got the first level IF to work but
the 2nd level failed - here's my current formula:
=IF(E3>DATE(2005,2,14),(IF(D3<DATE(2005,6,6),0,1)),2) -- I was trying to use
the DATE function to make the conversion work - it seemed to work correctly
on the first IF but not the second. BOTTOM LINE - what is the correct way
to compare dates (one is greater than or less than the other) ? Thanks.

 

[Dave Peterson]

I think your formula looks ok, but if your mm/dd/yy dates are not be compared
correctly, then I'd check to see if those "dates" are really dates. And if
you're in the right century.

Format those cells in a "prettier" fashion.
A custom format of:
mmm dd, yyyy
would make sure your dates are what you think they are.

If you change the format and the value didn't change, then it wasn't a date.
(some type of text??).

If you see that 2/3/05 appears as:
Mar 02, 1905
You'll see if there's a different problem.

But once you see what the real date is, you may see that your formulas were
evaluating exactly correctly.

 

[Guest]

Weird -- I converted the date format to mmm-dd-yyyy as you suggested (well, a
slight change with the dashes) - once the dates got "converted" the formula
started behaving properly. There didn't appear to be any inappropriate
dates. Go figure. Just for your point of reference - the dates were
originally exported into Excel from MS Project Server.

The wierd thing is -- I must "double-click" each date, then hit enter (edit,
then exit) in order for the date to "convert" (right-click, select format
doesn't convert it right away). What does that mean and is there an easier
way then doing this to a thousand dates?

On a final note - its like my "sub-concious" sent me this reply. My name is
David Peterson also!

Thanks for your help.

 

[Guest]

Ok - it clicks now. Makes sense!

Homers! Never heard of them - out here, Dunkin Donuts is the place to be!

Thanks for your help!

 

[Guest]

OK - I Need Your Help one More Time - now the "Nested IF" statement won't
work - can you look at this and let me know if I've formatted it wrong?

IF (E3>DATE(2005,2,14),(IF (D3<DATE(2005,6,6),(IF (D3 < DATE(2005,2,14),(IF
E3 > DATE
(2005,6,6),NETWORKDAYS(2/14/2005,6/6/2005)*F3,NETWORKDAYS(2/14/2005,E3)*F3),(IF
E3 > DATE(2005,6,6),NETWORKDAYS(D3,6/6/2005)*F3,NETWORKDAYS(D3,E3)*F3)),0)),0)

E3 equals the Ending date in the spreadsheet; D3 equals the start date; F3
is the value I'm mulitplying against the "number of working days between the
2 dates".

Thanks. (David Peterson)

 

[Dave Peterson]

I didn't look at all the formula, but this popped out:

=NETWORKDAYS(2/14/2005,6/6/2005)

You have to make sure that excel sees those things as dates:

=NETWORKDAYS(date(2005,2,14),date(2005,5,6))

You could use =datevalue(), but I find =date() just too darn nice.

(Homer was a reference to the Simpsons--he hears voices in his head lots of
times. Mostly just reminding him how good doughnuts are!)

OK - I Need Your Help one More Time - now the "Nested IF" statement won't
work - can you look at this and let me know if I've formatted it wrong?

IF (E3>DATE(2005,2,14),(IF (D3<DATE(2005,6,6),(IF (D3 < DATE(2005,2,14),(IF
E3 > DATE
(2005,6,6),NETWORKDAYS(2/14/2005,6/6/2005)*F3,NETWORKDAYS(2/14/2005,E3)*F3),(IF
E3 > DATE(2005,6,6),NETWORKDAYS(D3,6/6/2005)*F3,NETWORKDAYS(D3,E3)*F3)),0)),0)

E3 equals the Ending date in the spreadsheet; D3 equals the start date; F3
is the value I'm mulitplying against the "number of working days between the
2 dates".

Thanks. (David Peterson)

 

[Guest]

Ok - that did it! You're right, the DATE() field is sweet - and the only
viable option to make it work.

Thanks again for your help!!

 

[Dave Peterson]

Hmmmmmmmmmmm. Sweet--like doughnuts.

<vbg>


Glad you got it working.

Ok - that did it! You're right, the DATE() field is sweet - and the only
viable option to make it work.

Thanks again for your help!!