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, |
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 |
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, |
All times are GMT +1. The time now is 02:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com