Weeks and Months

[jumpyjim]

Hello, I was wondering if someone could easily explain a solution to this.

A B C D
1 Date Months Weeks Calculated date

2 01/01/2000 9 =(DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)))
3 01/01/2000 36 =(DATE(YEAR(A3),MONTH(A3),DAY(A3)+C3*53/7))

I am trying to add firstly months to a given date which works fine but with the weeks calculation, how can I factor in the reality of 4.3333333 weeks in a month as well as accommodate leap years in the final date calculation. I need D2 and D3 to give an identical date through separate formulas.

Any suggestions?

Much appreciated.

[Bob Phillips]

Why not just use

=A2+C3*7

[jumpyjim]

Hi xld, thanks for that but it does not give the desired result. The examples have a date of 01/01/2000 then adding 9 months should give the same date as adding 36 weeks but they don't. The weeks calculation is always a few days out compared to the month calculation.

Changing D3 to =(DATE(YEAR(A3),MONTH(A3)+C3/4.33,DAY(A3))) seems to be giving the correct answer but will need to test it some more.

[westconn1]

The weeks calculation is always a few days out compared to the month calculation.

as 36 weeks is not = 9 months that would be the correct answer, what if it is 35 or 37 weeks, what result do you want then?
if you only want values for whole months, why not just remove the weeks column?

[Bob Phillips]

Might be correct in a few tested cases, but you cannot rely on it a 9 months will be a different number of weeks depending upon which 9 months, whether it spans a 29th Feb or not. You are comparing apples and pears.

[EirikDaude]

Doesn't banks and economic institutions have some rather silly notions of how many days constitutes a month, a year, etc? Could have something to do with that...

[Bob Phillips]

There is the 12 times 30 day year accounting calculation, but I have not come across on that assumes a month equals a fixed number of weeks.

[jumpyjim]

Thanks for the feedback. It seems best to simply remove weeks as an option. Thanks again