Custom date Calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nine - 23 Feb 2008 18:10 GMT

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

Reply to this Message

Ron Coderre - 23 Feb 2008 18:47 GMT

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)

>I would like to calculate the age in months between 2 custom date codes.
>
[quoted text clipped - 12 lines]
>
> Dave

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 20:56 GMT

>I would like to calculate the age in months between 2 custom date codes.
>
[quoted text clipped - 12 lines]
>
>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

Reply to this Message

Dave - 23 Feb 2008 22:09 GMT

>>I would like to calculate the age in months between 2 custom date codes.
>>
[quoted text clipped - 33 lines]
>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

Reply to this Message

Ron Rosenfeld - 23 Feb 2008 22:40 GMT

>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
[quoted text clipped - 4 lines]
>
>Dave

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

Reply to this Message