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(E3DATE(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.

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.



DTODDP wrote:

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(E3DATE(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

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.

"Dave Peterson" wrote:

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.



DTODDP wrote:

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(E3DATE(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

oooohhhhh. Voice from deep inside.

Those dates came over as text. By hitting F2, then enter, you told excel to see
them as real dates.

Since all you did was F2|enter, a quicker way is to just:
edit|replace
what: /
with: /
replace all

Then excel will say "I've got to take a look at this again!" and notice that
they're dates.

Another voice is popping up--It sounds like Homer's:
mmmmmm. Doughnuts.





DTODDP wrote:

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.

"Dave Peterson" wrote:

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.



DTODDP wrote:

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(E3DATE(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


--

Dave Peterson

Ok - it clicks now. Makes sense!

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

Thanks for your help!

"Dave Peterson" wrote:

oooohhhhh. Voice from deep inside.

Those dates came over as text. By hitting F2, then enter, you told excel to see
them as real dates.

Since all you did was F2|enter, a quicker way is to just:
edit|replace
what: /
with: /
replace all

Then excel will say "I've got to take a look at this again!" and notice that
they're dates.

Another voice is popping up--It sounds like Homer's:
mmmmmm. Doughnuts.





DTODDP wrote:

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.

"Dave Peterson" wrote:

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.



DTODDP wrote:

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(E3DATE(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


--

Dave Peterson

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 (E3DATE(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" wrote:

oooohhhhh. Voice from deep inside.

Those dates came over as text. By hitting F2, then enter, you told excel to see
them as real dates.

Since all you did was F2|enter, a quicker way is to just:
edit|replace
what: /
with: /
replace all

Then excel will say "I've got to take a look at this again!" and notice that
they're dates.

Another voice is popping up--It sounds like Homer's:
mmmmmm. Doughnuts.





DTODDP wrote:

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.

"Dave Peterson" wrote:

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.



DTODDP wrote:

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(E3DATE(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


--

Dave Peterson