• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for months

Tom22

Member
Hi,

I am looking for formula which gives me months which falling in 2019 year and months falling in 2019.

Like if my date start from 5/31/2018 and end date is 5/31/2019, then as only want months in only 2019 year hence my output will be 7 and blank in 2020.

Likewise if my start date is 5/31/2019 and end date is 5/31/2020 then in2019 my output will be 7 and in 2020 it will be 5.


Many thanks in advance
 

Attachments

p45cal

Well-Known Member
If the start date is 26 March 2019, and the end date 4 May 2019 (a duration of a month and a few days) and you wanted your months count for 2019, what should the answer be?:
1 because it 1 month and a few days rounded to the nearest count of months?
3 because the dates encroach on 3 calendar months (March, April and May)?

Likewise if the start date is 20 March 2019, and the end date 10 May 2019 (a duration of a month and a about 21 days) and you wanted your months count for 2019, what should the answer be?:
1 because there's only 1 full calendar month between the dates?
2 because 1 month and a 20 days (about 1.7 months) rounded to the nearest whole number of months is 2?
3 because the dates encroach on 3 calendar months (March, April and May)?
 
Last edited:

Tom22

Member
Hi p45cal.....You can consider only month and ignore date....if start date is 26 March 2019, and the end date 4 May 2019....then we will consider 3 months as we encroach on 3 calendar months... THanks
 

GraH - Guido

Well-Known Member
counting in full months, it might be
[C2] =MONTH(MIN($B2,"31/12/"&RIGHT($C$1,4)))-(MONTH(MAX($A2,"1/1/"&RIGHT($C$1,4)))-1)
[D2]=RIGHT(MIN((YEAR(B2)*100+MONTH(B2))*(YEAR(B2)>=RIGHT($D$1,4)*1),RIGHT($D$1,4)*100+12),2)*1
 

Attachments

Tom22

Member
Thanks GraH - Guido….

Few issues…I can see formulas working but as soon as I click on enable editing….it gives me #Values! Error message in 2019 columns.

Another one if you see row 20, we have 11 in 2019 and 2 in 2020 (13 months),,,where as if we manually calculate it comes only 12 months.

I know in my previous comments I have said to ignore day only consider months but after seeing results I guess we need to consider day as well.

Sorry for the confusion.

Thanks
 

GraH - Guido

Well-Known Member
as for the errors:
-> what is your local setting of dates? You might need to change to "12/31" in your Excel.
-> as per your requirements in #3: start date is in feb and end date is in 2020, so there are 11 months (including feb) in 2019.

That is why requirements should be very clear at the start, and p45cal was proficient in foreseeing the issue.
I guess that means formulae need to be revised drastically to include days.

Still what does it mean? If end date is end of that month, does the month count? If start date is end of month, start counting from next month? Is it then months "between".

We would be needing answers to p45cal's questions as well:
1 because there's only 1 full calendar month between the dates?
2 because 1 month and a 20 days (about 1.7 months) rounded to the nearest whole number of months is 2?
3 because the dates encroach on 3 calendar months (March, April and May)?
 

Chihiro

Excel Ninja
May be...
C2:
=IF(OR(YEAR(A2)=2019,YEAR(B2)=2019),MONTH(MIN(B2,DATE(2019,12,31)))-MONTH(MAX(A2,DATE(2019,1,1)))+1,0)
D2:
=IF(YEAR(B2)=2020,MONTH(MIN(B2,DATE(2020,12,31)))-MONTH(MAX(A2,DATE(2020,1,1)))+1,0)
 

p45cal

Well-Known Member
Based on a general formula to determine the extent of overlap:
MIN(End Dates)-MAX(Start Dates)
whose values when greater than 0 are the overlap, my solution could be a variant of the following (depending on the OP clarifying his requirements):
Change the Months 2019 and Months 2020 in cells C1:D1 to just numbers 2019 and 2020, then in cell C2:
=IFERROR(DATEDIF(MAX(DATE(C$1,1,1),$A2),MIN($B2,DATE(C$1+1,1,1)),"M"),"")
and copy across and down.

Note that the date 31 Dec 2019 is not the end of the year, it's midnight at the start of that day, there's another 24 hours to go…
The same applies to the dates in columns A & B; we need to know whether these dates are inclusive, that is the whole of the start date to the whole of the end date? or something else?

To help the OP best decide how he'd like the month counts to show, this formula in any spare cell in row 2 (keeping pure year numbers in cells C1:D1) and copied down and across, will give a rough guide to the number of months involved in each year:
=ROUND(YEARFRAC(MAX(DATE(C$1,1,1),$A2),MIN($B2,DATE(C$1+1,1,1)),1)*12,1)
 
Last edited:
Top