Date Format Correct, But Formula Error
 

Hi All,

this is driving me crazy...if anyone can help i'd be grateful.

My formula is:

=ROUND((I4 - MySheetY3) / 365,0)

so i'm just trying to subtract one date from the other and then
dividing by 365to get the rounded number of years difference

However, this results in an #value error. The trace error shows:

=Round (( 35828 - "31/06/1948" / 365, 0)

When looking at the trace error bit, the first date (I4) is being taken
as the serial number, not the date, whereas the date in Y3 is being seen
as a date.

So thinking it was a formating thing, I checked the cell formating and
it is in a date format. I've tried deleting all the columns and
starting again, painting formats, changing formats...everything!

Any ideas?

cheers, John


--
john_mc
------------------------------------------------------------------------
john_mc's Profile: http://www.excelforum.com/member.php...o&userid=32013
View this thread: http://www.excelforum.com/showthread...hreadid=531404

Date Format Correct, But Formula Error
 

Hi!

The problem is: "31/06/1948"

There is no June 31st so the "date" is being evaluated as a TEXT string.

Biff

"john_mc" wrote in
message ...

Hi All,

this is driving me crazy...if anyone can help i'd be grateful.

My formula is:

=ROUND((I4 - MySheetY3) / 365,0)

so i'm just trying to subtract one date from the other and then
dividing by 365to get the rounded number of years difference

However, this results in an #value error. The trace error shows:

=Round (( 35828 - "31/06/1948" / 365, 0)

When looking at the trace error bit, the first date (I4) is being taken
as the serial number, not the date, whereas the date in Y3 is being seen
as a date.

So thinking it was a formating thing, I checked the cell formating and
it is in a date format. I've tried deleting all the columns and
starting again, painting formats, changing formats...everything!

Any ideas?

cheers, John


--
john_mc
------------------------------------------------------------------------
john_mc's Profile:
http://www.excelforum.com/member.php...o&userid=32013
View this thread: http://www.excelforum.com/showthread...hreadid=531404