Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountDays? need to count days in range by month
I have random date ranges over the month break, but I need to get a day count
by month to calculate rev by month. Eg: Range: 10/15/06-12/31/06 I need to know # of days in Oct, Nov, Dec from that range. Was going to set up one column/month to then put the result into a formula to figure (total rev for deal / # days in deal)*# of days in deal in that month=revenue for month Hope that makes sense. Any help? thanks in advance, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountDays? need to count days in range by month
=sumproduct(--(text(a1:a10,"yyyymm")="200610"))
will count the number of days in October. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html MhyphenL wrote: I have random date ranges over the month break, but I need to get a day count by month to calculate rev by month. Eg: Range: 10/15/06-12/31/06 I need to know # of days in Oct, Nov, Dec from that range. Was going to set up one column/month to then put the result into a formula to figure (total rev for deal / # days in deal)*# of days in deal in that month=revenue for month Hope that makes sense. Any help? thanks in advance, -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountDays? need to count days in range by month
=SUMPRODUCT(--(A1:A20-DAY(A1:A20)+1=DATE(2006,10,1)))
for October -- --- HTH Bob (change the xxxx to gmail if mailing direct) "MhyphenL" wrote in message ... I have random date ranges over the month break, but I need to get a day count by month to calculate rev by month. Eg: Range: 10/15/06-12/31/06 I need to know # of days in Oct, Nov, Dec from that range. Was going to set up one column/month to then put the result into a formula to figure (total rev for deal / # days in deal)*# of days in deal in that month=revenue for month Hope that makes sense. Any help? thanks in advance, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fomula for number of days on each month from a date range | Excel Discussion (Misc queries) | |||
How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
Count cells within a date range | Excel Discussion (Misc queries) | |||
Calculate Days in a Month | Excel Discussion (Misc queries) | |||
how to extract a specific range of days (7 or 30) to make a chart | Excel Worksheet Functions |