Calculating dates (prior year)

[VegasNath]

Hi,
Please can anyone explain why using this formula works fine for the current year but not the prior year?

Capture3.JPG

The format of column A is general, being an import from an external data source.

[HansV]

The formula only works "fine" for the current month - look at the result in row 3! The formula uses the date format yyyymmdd as a number, e.g. today is 20110212 i.e. 20,110,212 = twenty million one hundred and ten thousand two hundred and twelve. This is not a number of days in any meaningful sense of the word.

[VegasNath]

I see what you mean.
Is there a way to convert A1 to a meaningful value in order to calculate?

[HansV]

Yes, for example

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

or

=DATE(TRUNC(A1/10000),MOD(A1/100,100),MOD(A1,100))

[VegasNath]

Great, Thanks Hans.
=VALUE(TODAY())-DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
..... appears to be accurate.

[HansV]

You don't need VALUE here.

=TODAY()-DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

should work - if Excel formats the result as a date, just clear the formatting (or set the number format to General).

[VegasNath]