I would like to calculate the age in months between 2 custom date codes.

The date code format is as follows: 10804Where the 3 most significant digits
= year, in this case 2008, and the 2 least significant digits = week of the
yearin this case 04. The highest possible week code is 52, the last week of
a given year. So using this example I would like to calculate the number of
months between 10804and 10229.

The problem is these numbers don't subtract very nicely since they are not
your normal format.

Is there a good solution to this question?

Thanks in advance for the help.

Dave

Try this (in 2 sections for readability):

=DATEDIF(DATE("20"&MID(A1,2,2),1,MOD(A1,100)*7),
DATE("20"&MID(B1,2,2),1,MOD(B1,100)*7),"m")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"nine" <(E-Mail Removed)> wrote in message
news:kkZvj.12215$(E-Mail Removed)...
>I would like to calculate the age in months between 2 custom date codes.
>
> The date code format is as follows: 10804Where the 3 most significant
> digits = year, in this case 2008, and the 2 least significant digits =
> week of the yearin this case 04. The highest possible week code is 52, the
> last week of a given year. So using this example I would like to calculate
> the number of months between 10804and 10229.
>
> The problem is these numbers don't subtract very nicely since they are not
> your normal format.
>
> Is there a good solution to this question?
>
> Thanks in advance for the help.
>
> Dave
>

On Sat, 23 Feb 2008 18:10:24 GMT, "nine" <(E-Mail Removed)> wrote:

>I would like to calculate the age in months between 2 custom date codes.
>
>The date code format is as follows: 10804Where the 3 most significant digits
>= year, in this case 2008, and the 2 least significant digits = week of the
>yearin this case 04. The highest possible week code is 52, the last week of
>a given year. So using this example I would like to calculate the number of
>months between 10804and 10229.
>
>The problem is these numbers don't subtract very nicely since they are not
>your normal format.
>
>Is there a good solution to this question?
>
>Thanks in advance for the help.
>
>Dave
>

Another problem is that it is not clear what your dates refer to.

You write that "The highest possible week code is 52, the last week of
a given year".

What dates, exactly, would that be. Because if it is, for example 12/25/2008 -
12/31/2008, then week 1 cannot start on 1/1/2008 since this year has 366 days.

There would be two days difference this year, depending on if you started
counting from the beginning, or the end of the year.

Another problem with regard to calculating the number of months between two
dates is that months can have anywhere from 28-31 days.

There are a different ways of determining week numbers, but we need to know
which one you are using.

There are also methods of "normalizing" month differences but, again, we need
to know which you want to use.
--ron

Ron Rosenfeld <(E-Mail Removed)> wrote:

>On Sat, 23 Feb 2008 18:10:24 GMT, "nine" <(E-Mail Removed)> wrote:
>
>>I would like to calculate the age in months between 2 custom date codes.
>>
>>The date code format is as follows: 10804Where the 3 most significant digits
>>= year, in this case 2008, and the 2 least significant digits = week of the
>>yearin this case 04. The highest possible week code is 52, the last week of
>>a given year. So using this example I would like to calculate the number of
>>months between 10804and 10229.
>>
>>The problem is these numbers don't subtract very nicely since they are not
>>your normal format.
>>
>>Is there a good solution to this question?
>>
>>Thanks in advance for the help.
>>
>>Dave
>>
>
>Another problem is that it is not clear what your dates refer to.
>
>You write that "The highest possible week code is 52, the last week of
>a given year".
>
>What dates, exactly, would that be. Because if it is, for example 12/25/2008 -
>12/31/2008, then week 1 cannot start on 1/1/2008 since this year has 366 days.
>
>There would be two days difference this year, depending on if you started
>counting from the beginning, or the end of the year.
>
>Another problem with regard to calculating the number of months between two
>dates is that months can have anywhere from 28-31 days.
>
>There are a different ways of determining week numbers, but we need to know
>which one you are using.
>
>There are also methods of "normalizing" month differences but, again, we need
>to know which you want to use.
>--ron



I don't need to be specific to the day in this case all I really need is the number of
months difference between the date codes given, so I can tolerate some rounding, if
needed.

As far as which method is used for determining week numbers, week 1 is the first full
week of the year. I hope that is what you are looking for.

Dave

On Sat, 23 Feb 2008 22:09:31 GMT, "Dave" <(E-Mail Removed)> wrote:

>I don't need to be specific to the day in this case all I really need is the number of
>months difference between the date codes given, so I can tolerate some rounding, if
>needed.
>
>As far as which method is used for determining week numbers, week 1 is the first full
>week of the year. I hope that is what you are looking for.
>
>Dave

Given that, Ron C's solution should work just fine for you.
--ron