• 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

  • Book2.xlsx
    15.6 KB · Views: 14
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:
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
 
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

  • monthInYears.xlsx
    22.7 KB · Views: 7
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
 
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)?
 
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)
 
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:
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)
HI,

I was looking for a solution for my problem and I almost found it here in your solution. However, if I use the first formula, "=IFERROR....." I get am not getting the correct number for one year. If I use the "=ROUND...." I get the correct answer, but it also populates the cells for years that is not in the range. I am attaching a file with both the formulas added and showing the results. Can you please help me edit the formula so it gives me a blank if the year is not in the range of dates? Thank you in advance. This was a life saver!
 

Attachments

  • Calculate_Months_From_Dates.xlsx
    17.2 KB · Views: 2
Hazra Hadee
As a new member, You should reread Forum Rules:
How to get the Best Results at Chandoo.org
Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
 
Back
Top