Number of Days in each month between two dates

Hi,

I am trying to write a formula that will show how many days for each month makes up a total date difference.

For example, in cell A I have a start date, for example Jan 30 2015.

In Cell B I have an End date, for example February 19.

So in total, there are 20 days (End - Start).

What I want to show is January made up 1 of those 20 days, and February made up 19 of those days, March made up 0 of those days, April make up 0 of those days, May made up 0 of those days, etc.

The formula I used for M1 Value (it should technically be M2 (month 2 for February) is:
=IF(DAY(E186)<=28,V186,DAY(E186))

But I am wondering if this is the right "way" to go about this?

This works when D2 has the month name in it.

I got the formula from here http://stackoverflow.com/questions/3...-certain-month

Please Login or Register  to view this content.

This will give you the number of days between dates and the days in each month between those dates.

Hi,

I noticed when using the formula,

=IF(AND(EOMONTH(D$1,0)>=$B5,EOMONTH(D$1,-1)<=$B5),EOMONTH(D$1,0)-$B5,IF(AND(EOMONTH(D$1,0)>=$A5,EOMONTH(D$1,-1)<=$A5),$A5-EOMONTH(D$1,-1),IF(AND(EOMONTH(D$1,-1)>=$B5,EOMONTH(D$1,0)<=$A5),EOMONTH(D$1,0)-EOMONTH(D$1,-1),"")))

on certain dates it is mis-counting. For example, when the start date is 3/31/2015 and end date is 4/5/2015, I am getting the correction number for March (0) bur for April it shows as 30. Another example is 4/5/2015 to 4/19/2015, it is showing as 25 days in April and not 14. Most of the other cells are correct when calculating.

But when I use the other formula:
=SUMPRODUCT((MONTH($B3+ROW($A$1:$A$10000)-1)=MONTH(1&E$1))*(($B3+ROW($A$1:$A$10000)-1)<=$A3))

It works - interesting! I am going to see if I can see what the error is.

There were at least 2 problems. One was an inconsistency in the date format and that wasn't being picked up. One date was MM/DD/YYYY and the other was DD/MM/YYYY. The other was a bad bit of logic.

This version seems to calculate correctly. It calculates like a week. For example if today is Monday, a week from today is Monday i.e. +7 days. The start day isn't counted. This gives the same results when you subtract the Start Date from the End Date.

Funny thing is, I couldn't get the SUMPRODUCT formula to calculate past April. It gave me all kinds of "funny" results...difference in date formats or something else, I haven't determined.

See this...

https://www.excelforum.com/showthread.php?t=1005845

If you use newdoverman's setup it would be sufficient to use this formula in D2 copied across and down

=MAX(0,MIN($A2+1,EOMONTH(D$1,0)+1)-MAX($B2+1,D$1))

If you want blanks instead of zeroes just custom format all cells as

0;;

See attached

Nice one DDL

If you don't want 0s and don't want Conditional Formatting, this longer version will eliminate the 0s

Formula:

Please Login or Register  to view this content.

Or, if using Excel 2007 or later...

=IFERROR(1/(1/MAX(0,MIN($A2+1,EOMONTH(D$1,0)+1)-MAX($B2+1,D$1))),"")

or this in D2

=MAX(0,MIN(EOMONTH(D1,0),$B2)-MAX(D1,$A2)+1)

In A2 start date
B2 End Date
In D1, D2...D12 are date (01.01.2015, 01.02.2015....01.12.2015)