Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of weeks past in current qtr | Excel Worksheet Functions | |||
Show week number in current month | Excel Discussion (Misc queries) | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
HELP - need to returns the current number of past month this year and ... | Excel Worksheet Functions | |||
how do i display the total number of days in the current month in. | Excel Discussion (Misc queries) |