"dd" <dd.dd wrote in message ...
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.