Calculating a Weekly Budget from Monthly numbers ...

I need to know what your definition of current month is. Is it the month in which the week ending date falls or is it the beginning of the week for the week ending?

For example, you have 4 days for the week ending 2/4 - this is correct for the week ending date.
But for 4/1 you have 6 days. There is only one day in April until this week ending date. There are 6 days in March before the week ending date.

Can you take a re-look at the table you provided to make sure the results are correct.

P.S. Also it isn't the number of days that an end date goes into the next month that counts - that's going to be zero if week end date is the basis for the calculation. For example, any week end date in April is not going to spill over into May.

What you do want to know is how many days there are in the previous month. It is possible that a week ending date in a month could have started in the previous month.

Take a look at the formulas in this workbook to see if they will help you out.

Just a thought.

If you can make use of a lookup table that numbers each monthly week number (1 to 4, 5 or occasionally 6) have a look at the attached. The whole year of 2017 dates is listed in column J. Then to get the week numbers of each month (1-4 / 1-5,6) this formula in column K.

Formula:

Please Login or Register  to view this content.

BTW: This forces the week to end on Saturdays.

I think I would need to sit down with you so we can both point to a calendar to know what we both mean by "week". From there I would know better how to carve this one up.

In the meantime I tried this in C5 of your upload

Formula:

Please Login or Register  to view this content.

and in B5

Formula:

Please Login or Register  to view this content.

I get the same as dflak.

That's what I would do.

BTW: Here is another handy formula: =DAY(DATE(YEAR(MYDATE),MONTH(MYDATE)+1,0)) This gives the number of days in the month containing MYDATE.
=DAY(DATE(YEAR(MYDATE),MONTH(MYDATE),0)) gives the number of days in the previous month.

Originally Posted by FlameRetired

I get the same as dflak.

That's what I like about Excel, there are so many different ways of doing the same thing.

@ dflak
Yes! And nearly every time I log on someone comes up with creative approaches I never dreamed of ... a real "think outside of the box" crew here.

What fun!