Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating number of days belonging to each month within a given week
Hello,
I want to calculate number of days belonging to each month within a given week, e.g. if i have week no 1 29Dec08 until 4Jan09 week no 2 5Jan09 until 11Jan09 week no 3 12Jan09 until 18Jan09 week no 4 19Jan09 until 25Jan09 week no 5 26Jan09 until 1Feb09 I would like to calculate the split of days belonging to December, January,February etc; in this case it would be week no 1 - 4 in Dec and 3 in Jan week no 2 - 7 in Jan week no 3 - 7 in Jan week no 4 - 7 in Jan week no 5 - 6 in Jan & 1 in Feb I've tried to use eomonth formula but even with Analysis Tool Pack it doesn't work. thank you. EggHeadCafe - Software Developer Portal of Choice JavaScript DatePicker http://www.eggheadcafe.com/tutorials...atepicker.aspx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating number of days belonging to each month within a given week
On Wed, 18 Nov 2009 03:32:54 -0800, kate zareba wrote:
Hello, I want to calculate number of days belonging to each month within a given week, e.g. if i have week no 1 29Dec08 until 4Jan09 week no 2 5Jan09 until 11Jan09 week no 3 12Jan09 until 18Jan09 week no 4 19Jan09 until 25Jan09 week no 5 26Jan09 until 1Feb09 I would like to calculate the split of days belonging to December, January,February etc; in this case it would be week no 1 - 4 in Dec and 3 in Jan week no 2 - 7 in Jan week no 3 - 7 in Jan week no 4 - 7 in Jan week no 5 - 6 in Jan & 1 in Feb I've tried to use eomonth formula but even with Analysis Tool Pack it doesn't work. thank you. EggHeadCafe - Software Developer Portal of Choice JavaScript DatePicker http://www.eggheadcafe.com/tutorials...atepicker.aspx The specifics depend on you have your data set up, and how you want to display the results. But, in general, with a Starting Date in A2 Ending Date in B2 The number of days in that range that are in the same month as the starting date: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A2&":"&B2)))=MONTH(A2))) The number of days in that range that are in the same month as the ending date: =SUMPRODUCT(--(MONTH(ROW(INDIRECT(A2&":"&B2)))=MONTH(B2))) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculating number of days belonging to each month within a given
If there is to be a split across 2 months, then the day of the month in the
ending date will be 1, 2, 3, 4, 5 or 6. Assuming your first date is in cell A2 and the second date is in B2, this formula will give you the number of days in the 1st month =IF(DAY(B2)<7,7-DAY(B2),7) and then (if that formula is in C2) you can use this to get the number of days in the second month: =7-C2 "kate zareba" wrote: Hello, I want to calculate number of days belonging to each month within a given week, e.g. if i have week no 1 29Dec08 until 4Jan09 week no 2 5Jan09 until 11Jan09 week no 3 12Jan09 until 18Jan09 week no 4 19Jan09 until 25Jan09 week no 5 26Jan09 until 1Feb09 I would like to calculate the split of days belonging to December, January,February etc; in this case it would be week no 1 - 4 in Dec and 3 in Jan week no 2 - 7 in Jan week no 3 - 7 in Jan week no 4 - 7 in Jan week no 5 - 6 in Jan & 1 in Feb I've tried to use eomonth formula but even with Analysis Tool Pack it doesn't work. thank you. EggHeadCafe - Software Developer Portal of Choice JavaScript DatePicker http://www.eggheadcafe.com/tutorials...atepicker.aspx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating as per the number of days in a month | Excel Discussion (Misc queries) | |||
Days per month for calculating storage days | Excel Worksheet Functions | |||
How can I add up lookups? Finding days in a week of a month | Excel Discussion (Misc queries) | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Calculating days in a month | Excel Discussion (Misc queries) |