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

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" <john_mc.26136n_1144638603.7327@excelforum-nospam.com> wrote in
message news:john_mc.26136n_1144638603.7327@excelforum-nospam.com...
>
> 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
>