Days count as per month

[Veeru106]

Hi,
I am looking for a solution, where days should be calculated from begining date to end date and another thing....days for that particular month should be under that month...for example if my begining date is 26-05-2018 (dd-mm-yyyy) and my end date is 31-03-2019..then it should show number of days starting from May month (5 days) then in june (30 days) in july(31 days) and so on..
Attaching sample file for calculation.
Please suggest

 

[AlanSidman]

So long as the end date is after the end of the month for the start date, then this formula should work

=DATEDIF(EOMONTH(A5,0),B5,"d")

 

[Veeru106]

Not working.....i have tried on Cell A5 n B5 and as per your formula number is 118 which is wrong as in simple terms it should be 123....attching revised sheet

 

[AlanSidman]

In your first post, you indicated that you wanted it to give you the days between the first of the next month and not from the actual start date. Hence the difference. If this is not what you want, then please clarify. If you are looking for a straight difference, then you have your answer of doing subtraction and I wonder why if you knew that you posted here at all.

If I am not understanding your needs, then I suggest you post your solution manually so that we can give you what you need. But only a sample solution. Because, now I am confused.

 

[Veeru106]

Sorry for the confusion....As i mentioned in my first post....simple subtraction from begining date to end date will give me combine figure...that is not wanted...i have done manual calculation in A5 cell...where we can see start date is 26-10-2018 and end date is 26-2-2019....hence starting from oct month we have number of days till Feb-2019...total is 123 days.

Hope that helps

 

[AlanSidman]

Your calculation includes 5 days in October which you just stated you wanted to exclude. 123-5=118, which is the answer my formula provided in post #2.

 

[AliGW]

In C5 copied across:

=IF(AND($B5>=C$2,$B5<D$2),$B5-C$2,IF(C$2>$B5,0,MAX(0,EOMONTH(C$2,0)-$A5-SUM($B5:B5)+$B5)))

 

[Veeru106]

It is good and thanks for taking time for this @AliGW…but some cells it is not working fine like Cell 3….where our beginning date is 10-09-2018 and end date is 23-09-2018…. Hence my result should be 13 but it is coming as 22..

Same as for cell 2...it is coming as 4, where it should be 1

Please suggest

 

[Veeru106]

That’s not right Alansidman…..as my file clearly show that we have 5 days in oct , 30 in nov,31 in dec,31 in jan and 26 in feb


Total is 123…

 

[bosco_yip]

Try,

In C2 copied across and down :

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

Regards
Bosco

 

[vletm]

Veeru106
... or if no need those 3696 formulas there,
then press right-top [ Do It ]

 

[Veeru106]

Thanks bosco and vletm.....it is working....appreciate it..

 

[bosco_yip]

Try,

In C2 copied across and down :

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

Regards
Bosco

Hi,Veeru106

Please confirm your duration calculation

1] Generally duration calculation :

=End data-Start date+1

Thus, my post #.10 formula used

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

2] I re-read your post #.8 and #.9, you appeared want the duration to calculate:

=End data-Start date

that is 1 day difference from the above formula

Then,

the post #.10 formula should be revised to (in remove +1 to the above formula):

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

3] However, in your worksheet row 140 and 141, Start date and End date both is in the same date 01-09-18

Formula [1] will give you 1, and

formula [2] will give you blank(0)

Please made your choice to meet with your condition.

Regards
Bosco

 

[Veeru106]

Sorry for the confusion Bosco.....your solution in post 10 is working fine.....
Thank you for your time....

 

[AlanSidman]

Obviously, I misunderstood the criteria here. Glad to see you have it resolved.

 

[Veeru106]

No problem .....luckily we have more than one champ here....anyways thanks for taking time out for this