Convert days into weeks and months

[Rudi]

Hi,

If I have 1112 days, what formula can I use to accurately convert these days into weeks and months.

Weeks is easy: 1112 days / 7 = 159 weeks
But months cannot simply be divided by 30, as some are 30, 31, 29 and 28?? What can I do here?

Any suggestions for accurate answers appreciated.

[HansV]

In this particular example, the answer is 36 months, regardless of the start date.

In general, it depends on the start date, of course.
With the start date in A1, and the end date in B1, you can use this formula to count the number of calendar months between the dates:

=DATEDIF(A1,B1,"m")

See DATEDIF Worksheet Function.

[Rudi]

The query I received from a customer does not have a start date and end date. It is a number representing days. In a cell there is 1112 as a days entry. I need to find out accurately to how many months this works out to?

TX

[HansV]

The average number of days per month is 1461 / 48 = 30.4375 (4 years = 4 * 365 days + 1 extra for the leap year = 1461 days; 4 years = 4 * 12 = months).

1112 / 30.4375 ~ 36.5 months, so there will always be 36 whole months between the dates (plus 16 or 17 days).

[Rudi]

Cheers. Thanks for that formula. :)

[ChrisGreaves]

... (plus 16 or 17 days).

Can I make the following assertion:
If the fractional part of the result is within +- 1/30.4375 i.e. less than or equal to 0.032854, then the number of months may depend on the start date, but
If the fractional part of the result is outside +- 1/30.4375 i.e. greater than 0.032854, then the number of months may be considered accurate.
February, as usual, messes things up.

[HansV]

February, as usual, messes things up.

Yep. From February 1, 2010 to March 1, 2010 is 1 month, but it is only 28 days, i.e. 0.92 of 30.4375 days, so your "rule" doesn't hold.