How do I count the number of even weeks in the current month.

[dd]

I receieve payments every two weeks.

I have a budget spreadsheet and I want to set up a function which tells me
the number of even-numbered weeks in the current month. I can then multiply
this figure, by the amount of each payment and include it in my budget
calculations.

Also, I want to know how to identify the start and end of month weeknumbers,
for each month. I can then use arguments to update the budget when the
payments are actually received?

Regards
DDawson
Scotland

 

[Bob Phillips]

Am I missing something? As a month can only have 4 or 5 weeks (depending
upon how you define the week start), there will always be 2 even numbered
weeks.

On the second part, using MS week numbers

=WEEKNUM(A1-DAY(A1)+1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[dd]

Bob,

Do the weeks not go from 1 to 52?

Dylan

Am I missing something? As a month can only have 4 or 5 weeks (depending
upon how you define the week start), there will always be 2 even numbered
weeks.

On the second part, using MS week numbers

=WEEKNUM(A1-DAY(A1)+1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike]

This is going to be very difficult to solve. For example it is quite possible
for the first few days of January to be week 52 or even (surprisingly) week
53 of the previous year. When the latter happens ther will be 2 consecutive
odd weeks (53 - 1) which will not help your cashflow. You may be able to
build on theis

=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7)

which will return the week number.

Mike

 

[dd]

Thanks mike

I originally used
=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),{1E+99,7})*{1,-1})+5)/7)
But =TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7) seems quicker.

I got the start and end dates using
=DATE(YEAR(NOW()),MONTH(NOW()),1)
and
=DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1

I get the number of even weeks using
=ROUND((End Week No-Start Week No)/2,0)

I need a statement which will multiply (consecutuvely) the value, so that,
when the even-weeks occur the value increases.

So that when the first even-week comes around the cell value changes to £100
When the second even week occurs the value changes to £200
And if there is a third even week, the value changes to £300

Regards
Dylan

This is going to be very difficult to solve. For example it is quite
possible
for the first few days of January to be week 52 or even (surprisingly) week
53 of the previous year. When the latter happens ther will be 2 consecutive
odd weeks (53 - 1) which will not help your cashflow. You may be able to
build on theis

=TRUNC(((B6-DATE(YEAR(B6),1,0))+6)/7)

which will return the week number.

Mike

 

[Bob Phillips]

They do, but it is where week 1 starts that is the key that I am asking
about.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Hans Terkelsen]

I receieve payments every two weeks.

I have a budget spreadsheet and I want to set up a function which tells me
the number of even-numbered weeks in the current month. I can then multiply
this figure, by the amount of each payment and include it in my budget
calculations.

Also, I want to know how to identify the start and end of month weeknumbers,
for each month. I can then use arguments to update the budget when the
payments are actually received?

Regards
DDawson
Scotland

DD, you want some step function, if I understand you, that gives the total for the current month, with payments every two weeks.

Assume in A1 some startdate for the very first payment when you started.
Say 1Jan2007 in A1. A monday.

A date of the current month in B1.

The current month total could be
=(100*(INT((B1-$A$1)/14)-INT((B1-DAY(B1)-$A$1)/14)))
This adds 100 every second monday stepping up from 0 in the current month.
Counting from the start date.

Involving the european weeknumber makes it more complicated,
especially since some years have 53 european weeks,
and I don't suppose you want sometimes to have a 3 week interval between payments.

Take the (ISO) weeknumber of 28dec to see how many european weeks a year has.
2009 has 53.

Microsoft doesn't provide a reliable european weeknumber,
so there is a small industry in ISO weeknumbers.
Here are two more using only simple functions:

On Excel Worksheet:

(1/1/1900-28/12/2104)
=INT(MOD(INT((d+2924)/7)*28,1461)/28+1)

(All dates)
=INT(MOD(MOD(MOD(INT((d+692501)/7),20871)*28+4383,146096),1461)/28+1)

As VBA function:
Function WkIso(d) '..1/1/100-31/12/9999 gregorian...
WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1
End Function

But for the step function you wanted they are not directly required.

Hope you can use that, Hans.