Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dd dd is offline
external usenet poster
 
Posts: 95
Default How do I count the number of even weeks in the current month.

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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default How do I count the number of even weeks in the current month.

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" <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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dd dd is offline
external usenet poster
 
Posts: 95
Default How do I count the number of even weeks in the current month.

Bob,

Do the weeks not go from 1 to 52?

Dylan

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





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,101
Default How do I count the number of even weeks in the current month.

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" wrote:

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dd dd is offline
external usenet poster
 
Posts: 95
Default How do I count the number of even weeks in the current month.

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

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

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







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default How do I count the number of even weeks in the current month.

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)



"dd" <dd.dd wrote in message ...
Bob,

Do the weeks not go from 1 to 52?

Dylan

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







  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 31
Default How do I count the number of even weeks in the current month.


"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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Number of weeks past in current qtr AD108 Excel Worksheet Functions 2 December 4th 06 02:17 PM
Show week number in current month DKerr Excel Discussion (Misc queries) 4 February 23rd 06 10:20 PM
Formula for current month minus one = Quarter number in a macro. Pank Excel Discussion (Misc queries) 11 June 22nd 05 02:47 PM
HELP - need to returns the current number of past month this year and ... elz64 Excel Worksheet Functions 6 April 6th 05 01:37 PM
how do i display the total number of days in the current month in. timerigger Excel Discussion (Misc queries) 6 March 20th 05 06:13 PM


All times are GMT +1. The time now is 06:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"