How do i write a formula to count the number of days in a date range, split per month

Hi, sorry if this has previously been posted and solved but the search funtion doesnt work on my browser at work.

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. im currently trying to figure out a way to identify the number of days, per calender month, that falls in a date range.

sample data...

Start Date	End Date	Old Value	New Value
08/03/2010	18/06/2010	16758.2	16758.1
19/06/2010	04/08/2010	16758.2	-224147.3
05/08/2010	17/01/2011	16758.2	2923.1

i need to break down the total number of days per month

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
		24	30	31	18
					12	31	4
							27	30	31	30	31

As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...

I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.

(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it)
can any one help at all please?

Ta
Ben

Hi Ben and welcome to the forum

You can use SUMPRODUCT for this. =SUMPRODUCT(MONTH(A1:A500))=......

sOMETING LIKE THIS.

wOULD YOU LIKE TO UPLOAD A SAMPLE WORKBOOK??

This formula should help you.

=IF(ISERROR(DATEDIF(MAX([Start Date],[Current Month]),MIN([End Date],[Next Month]),"d"))=TRUE,"",DATEDIF(MAX([Start Date],[Current Month]),MIN([End Date],[Next Month]),"d"))

Hello Ben,

Assuming you have start date in A2 and end date in B2 try putting the 1st of each month in F1 across and then you can use this formula in F2 copied across and down

=MAX(0,MIN($B2,G$1-1)-MAX($A2,F$1)+1)

See attached. Note that I formatted row 1 to show just mmm-yy and I formatted the result cells so that zero displays as blank. With that formula you need the date headers to go one month further than the data.....[so if you put Feb-11 in S1 you'll get 17 in R4]

Originally Posted by benwwatson

Hi Guys thanks for this, sadly i cant upload a spreadsheet as i am working with sensetive information... i will try and create a dummy in in a bit.

Chappo, this gives me the total days over the period, i need to split it by month. march as a single figure needs to say "24" inclusive of the 8th if possible.

Just FYI, the formula I provided did split by month.