Custom date Calculations

  • Thread starter Thread starter nine
  • Start date Start date
N

nine

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)
 
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
Click to expand...

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 said:
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
Click to expand...



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
 
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
Click to expand...

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