Days between two dates separated into days per month

[wjohnson1976]

Hello all,

I am trying to make an irrigation water demand worksheet for engineers and I am almost finished, but the date entry is perplexing me...

I want a user to be able to enter two dates then the days per month between the two populate a column as shown below. For example user enters "05/20/2010" and "09/14/2010"

Month Days
Jan 0
Feb 0
Mar 0
Apr 0
May 11
Jun 30
Jul 31
Aug 31
Sep 14
Oct 0
Nov 0
Dec 0

I tried using nested ifs with text and it was getting way out of hand for me. I realize the total number of days between can be easily displayed but the jump to separate months and let the other months=0 is out of my league!

Thanks in advance!

W. Johnson

 

[AlphaFrog]

Excel Workbook
	A	B
1	Start Date	End Date
2	5/20/2010	9/14/2010
3
4	Month	Days
5	Jan	0
6	Feb	0
7	Mar	0
8	Apr	0
9	May	11
10	Jun	30
11	Jul	31
12	Aug	31
13	Sep	14
14	Oct	0
15	Nov	0
16	Dec	0
...

Cell Formulas
Range	Formula
B5	=MAX(0,MIN(DATE(2010,ROW(2:2),0),$B$2) - MAX(DATE(2010,ROW(1:1),0),$A$2))
B6	=MAX(0,MIN(DATE(2010,ROW(3:3),0),$B$2) - MAX(DATE(2010,ROW(2:2),0),$A$2))
B7	=MAX(0,MIN(DATE(2010,ROW(4:4),0),$B$2) - MAX(DATE(2010,ROW(3:3),0),$A$2))
B8	=MAX(0,MIN(DATE(2010,ROW(5:5),0),$B$2) - MAX(DATE(2010,ROW(4:4),0),$A$2))
B9	=MAX(0,MIN(DATE(2010,ROW(6:6),0),$B$2) - MAX(DATE(2010,ROW(5:5),0),$A$2))
B10	=MAX(0,MIN(DATE(2010,ROW(7:7),0),$B$2) - MAX(DATE(2010,ROW(6:6),0),$A$2))
B11	=MAX(0,MIN(DATE(2010,ROW(8:8),0),$B$2) - MAX(DATE(2010,ROW(7:7),0),$A$2))
B12	=MAX(0,MIN(DATE(2010,ROW(9:9),0),$B$2) - MAX(DATE(2010,ROW(8:8),0),$A$2))
B13	=MAX(0,MIN(DATE(2010,ROW(10:10),0),$B$2) - MAX(DATE(2010,ROW(9:9),0),$A$2))
B14	=MAX(0,MIN(DATE(2010,ROW(11:11),0),$B$2) - MAX(DATE(2010,ROW(10:10),0),$A$2))
B15	=MAX(0,MIN(DATE(2010,ROW(12:12),0),$B$2) - MAX(DATE(2010,ROW(11:11),0),$A$2))
B16	=MAX(0,MIN(DATE(2010,ROW(13:13),0),$B$2) - MAX(DATE(2010,ROW(12:12),0),$A$2))

 

[MickG]

Hi, Try this:-

[COLOR="Navy"]Sub[/COLOR] MG14Aug13
[COLOR="Navy"]Dim[/COLOR] stdt [COLOR="Navy"]As[/COLOR] Date, EdDt [COLOR="Navy"]As[/COLOR] Date, oDys [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c, m, mm
stdt = Range("C1")
EdDt = Range("D1")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] oDys = stdt To EdDt
        [COLOR="Navy"]If[/COLOR] Month("1/" & Dn & "/2010") = Month(oDys) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] oDys
Dn.Offset(, 1) = IIf(c > 0, c, 0)
c = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(A) [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(B) [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(C)  [/B][/COLOR] [COLOR="RoyalBlue"][B]Column(D)  [/B][/COLOR]
1.      Jan        17         15/01/2010  20/12/2010 
2.      Feb        28                                
3.      Mar        31                                
4.      Apr        30                                
5.      May        31                                
6.      Jun        30                                
7.      Jul        31                                
8.      Aug        31                                
9.      Sep        30                                
10.     Oct        31                                
11.     Nov        30                                
12.     Dec        20

Regards Mick





Regards Mick

 

[wjohnson1976]

Thanks for the responses!

@ MickG, brilliant! I am quite new to macros and subs and all of this so thanks. Maybe a naive question but is there a way to force macros to run automatically? I really am aiming for something where adjustments to the dates will reflect instantly on the greater data calculations...but if not, you still have helped immensely so thank you.

@ AlphaFrog, I like the fact that there is not a macro to run after each update, however January simply does not work. Am I missing something?

 

[AlphaFrog]

This is what I got for January. Same formulas as before.

Excel Workbook
	A	B
1	Start Date	End Date
2	1/21/2010	9/14/2010
3
4	Month	Days
5	Jan	10
6	Feb	28
7	Mar	31
8	Apr	30
9	May	31
10	Jun	30
11	Jul	31
12	Aug	31
13	Sep	14
14	Oct	0
15	Nov	0
16	Dec	0
...

Cell Formulas
Range	Formula
B5	=MAX(0,MIN(DATE(2010,ROW(2:2),0),$B$2) - MAX(DATE(2010,ROW(1:1),0),$A$2))

Could you better explain what doesn't work? Do you get an error? Is the Days wrong?

 

[MickG]

Hi, Try this:-
This is a worksheet _Change Event, when you change the value in "D1" the code will run. (Change Target Address at top of code if Required)
Right click sheet Tab, Select "View Code", VB window appears.
Paste the Entire code into the Vb window.
Close Vb Window.
Change "D1" value , code runs.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] stdt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] EdDt [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] oDys [COLOR="Navy"]As[/COLOR] Date
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.address(0, 0) = "D1" [COLOR="Navy"]Then[/COLOR]
stdt = Range("C1")
EdDt = Range("D1")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] oDys = stdt To EdDt
        [COLOR="Navy"]If[/COLOR] Month("1/" & Dn & "/2010") = Month(oDys) [COLOR="Navy"]Then[/COLOR]
            C = C + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] oDys
Dn.Offset(, 1) = IIf(C > 0, C, 0)
C = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[wjohnson1976]

@ AlphaFrog, you are right...it does work as you said! Sorry, I think I pasted the code into previously formatted cells maybe. It was coming up zero in January before. Anyway, when I created a whole new sheet and tried, it worked fine. GREAT WORK. You rule.

@ MickG, yours works well too! Will definitely keep this for a future use in case I want any code to run when I change a specific cell. You rule as well.

As for which one I will use today? I don't know! but I wish I could give you both credit when I present my little program. Couldn't have done this without you.

Peace!

 

[fshhady]

@AlphaFrog
It's really great solution. But my problem is to handle more than one start day and more than end day correspondingly.

For Example:
StartDate EndDate
Jan 1, 2015 Jan 5, 2015
Jan 27, 2015 Feb 2, 2015
Feb 20, 2015 Mar 15, 2015
Mar 1, 2015 May 2, 2015
Mar 3, 2015 Mar 20, 2015

So I need one formula per month to calculate all the dates at once.

Jan	?
Feb	?
Mar	?
Apr	?
May	?
Jun	?
Jul	?
Aug	?
Sep	?
Oct	?
Nov	?
Dec	?

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I tried to convert your formula to array formula but I have problem with Min and Max. I cannot return Min and Max for each row in the array.

I appreciate any help

 

[AlphaFrog]

I'm not the best at array formulas. There's probably a more elegant solution than this, but it works (limited to dates within the same year).

Excel Workbook
	A	B
1	Start Date	End Date
2	Jan 1, 2015	Jan 5, 2015
3	Jan 27, 2015	Feb 2, 2015
4	Feb 20, 2015	Mar 15, 2015
5	Mar 1, 2015	May 2, 2015
6	Mar 3, 2015	Mar 20, 2015
7
8	Month	Days
9	Jan	10
10	Feb	11
11	Mar	64
12	Apr	30
13	May	2
14	Jun	0
Sheet

 

[fshhady]

@AlphaFrog, Thank you I really great help.
But I still thinking how to do the same using Max and Min function. Since I tried a lot to return the min/man for each row using one formula array, I wonder if that is possible?.
Thank you again
Best Regards