Find the month between two dates

[Verbamore]

Hi everybody, I have a quick question.
I ma trying to find the month between two dates for example :

8/31/2013(placed on the cell A1)-10/1/2013(placed on the cell B1)
For doing this i use the formula =IF(DAY(A1)>14,MONTH(B1),MONTH(A1))
In this case the result is 10(October), which is wrong result because this period of time has more days in 7(September) so the period should be in 7.

My question is: Is it possible to find a formula that find the month based on number of the day; for example, less then 15 days should be on the current month, more then 15 days on the next month.

I would really appreciate if you find me a solution.

[techflutter]

Hi,

The dates is actually based on a number system. Excel starts counting at 1-Jan-1900 so pop this date into you cell and then convert the cell to a number.

You can then place the next date in the cell below, convert that to a number and see what difference it gives you.

I would then write your code on this basis.

James

[Jack7774]

Rather than finding the month between if its always only one month in between you could just use this to find the month prior and A1 would be referencing 10/1/2013

Please Login or Register  to view this content.

[Verbamore]

Thanks for your advice but doesn't work. I see the same exactly result. I think it's logic issue. For excel is the same, either way, date format or number format

[Tony Valko]

Your explanation is very confusing.

Maybe if you posted several examples and showed us what results you expect it will help us understand what you're wanting to do.

[Verbamore]

Rather than finding the month between if its always only one month in between you could just use this to find the month prior and A1 would be referencing 10/1/2013

Please Login or Register  to view this content.

Its always one month in between and using this shows me only the month of the cell part on the formula.
I think this problem is well know in all business company as the 15th of the month,, so for example if the day is the 15th day of the month what do we have to do, include it in current month or at the one after. my idea is to not use two different sources as in my formula above{=IF(DAY(A1)>14,MONTH(B1),MONTH(A1))}; month in cell A1 and month in the cell b1. I wonder if there is any way to say to excel if the day in the first value is greater then 15, write the month after, if not write the current value. not sure if I can resolve this with macro.

[Verbamore]

Your explanation is very confusing.

Maybe if you posted several examples and showed us what results you expect it will help us understand what you're wanting to do.

so using my formula =if(day(a1)>14,month(b1),month(a1)) doesn't work in all cases
10/23/2013 11/19/2013 11
11/20/2013 12/20/2013 12
12/21/2013 1/27/2014 1
1/28/2014 2/25/2014 2
8/31/2013 10/1/2013 10
5/1/2013 5/29/2013 5

The one in bold should be 9(September) because the majority of the days are in September.
I wonder if there is any other formula for telling excel, if the day in the start day is grater then 15, the month should be the month following(since the number of the day in one month is 30 and if the start day is after the 15th day of the current month that mean than most part of the day should be on the month after), if thestart day is before the 15th of the month than its the current month.

[Tony Valko]

I wonder if there is any other formula for telling excel, if the day in the start day is grater then 15, the month should be the month following(since the number of the day in one month is 30 and if the start day is after the 15th day of the current month that mean than most part of the day should be on the month after), if thestart day is before the 15th of the month than its the current month.

OK, that part is simple. But then you say this:

The one in bold should be 9(September) because the majority of the days are in September.

So what's the rule regarding majority of the days being in one month?

This sounds like a nightmare!

[gak67]

Please Login or Register  to view this content.

It's a complicated formula for something I don't believe is a problem.

I think this problem is well know in all business company as the 15th of the month,, so for example if the day is the 15th day of the month what do we have to do, include it in current month or at the one after

I have worked in many commercial environments and I've never had this issue.

The formula also assumes there is not an instance where the date range covers more than three months.

[Verbamore]

OK, that part is simple. But then you say this:


So what's the rule regarding majority of the days being in one month?

This sounds like a nightmare!

What do you mean by that?
Let me explain:
If you take this two dates 8/31/2013 - 10/1/2013 you'll see: the period that has start date(8/31/2013) and end date(10/1/2013) is composed by three different calendar months:
August(8) by 1 day(the last day of the month)
September(9) by 30 days
and October(10) by 1 day(the first day of the month)
So based on this, the month should be September(9) since we have more then 15 days for September.
If you help me to find a formula for this, I would really appreciate it.

[Tony Valko]

Show us about 10 examples and show us what results you expect.

[gak67]

Show us about 10 examples and show us what results you expect.

OP has already given 6 (post #7). Is that not enough?

[Tony Valko]

I think we need to see more that fall into this category:

The one in bold should be 9(September) because the majority of the days are in September.

[AlKey]

Maybe this

=MONTH(MEDIAN(A1,B1))

	A	B	C
1	10/23/2013	11/19/2013	11
2	11/20/2013	12/20/2013	12
3	12/21/2013	1/27/2014	1
4	1/28/2014	2/25/2014	2

[Verbamore]

Show us about 10 examples and show us what results you expect.

Please see the example

[Verbamore]

Maybe this

=MONTH(MEDIAN(A1,B1))

	A	B	C
1	10/23/2013	11/19/2013	11
2	11/20/2013	12/20/2013	12
3	12/21/2013	1/27/2014	1
4	1/28/2014	2/25/2014	2

This looks fine. Can you give me an explanation what this formula is doing:
It's still a gap for the situations like this 12/17/2013 - 1/14/2014, when the number of days for both months(Dec and Jan) is the same 14 days.
Thanks for your help

[Verbamore]

This looks fine. Can you give me an explanation what this formula is doing:
It's still a gap for the situations like this 12/17/2013 - 1/14/2014, when the number of days for both months(Dec and Jan) is the same 14 days.
Thanks for your help

For example this formula posted by gak67 :
=IF(MONTH(A1)=MONTH(B1),MONTH(A1),IF(MONTH(EDATE(A1,1))=MONTH(B1),IF(EOMONTH(A1,0)+1-A1>B1-EOMONTH(B1,-1),MONTH(A1),MONTH(B1)),MONTH(EDATE(A1,1))))
for the same period of time(12/17/2013 - 1/14/2014) calculate different month

[Verbamore]

Please Login or Register  to view this content.

It's a complicated formula for something I don't believe is a problem.I have worked in many commercial environments and I've never had this issue.

The formula also assumes there is not an instance where the date range covers more than three months.

Thanks for your time doing this for me. Can you explain to me what this formula is doing, i want to have a better understanding.
Another member posted another formula which is-> =MONTH(MEDIAN(A1,B1))

if you apply this two formula for example in one case like this(12/17/2013 - 1/14/2014) you'll see two different results.

[Tony Valko]

It's still a gap for the situations like this 12/17/2013 - 1/14/2014, when the number of days for both months(Dec and Jan) is the same 14 days.
Thanks for your help

What result do you expect for that example?

[gak67]

I question your logic on some of your dates.

Row 4 for instance, 17 Dec to 31 Dec is 15 days (inclusive), while 1 Jan to 14 Jan is only 14 days (inclusive), yet you say it should be Jan (1). Based on your logic, which seems to be ignoring the first day of the range, try this formula:

Please Login or Register  to view this content.

[Verbamore]

What result do you expect for that example?

This depend by the situation.
We do reports based on 12-months and months should be in continuity. For example. after we find the proper formula and apply it, we'l have a result like this:
1 2 3 4 6 6 7 8 9 10 11 12 we need May, so in this situation I have to change it manually. 1 2 3 4 5 6 7 8 9 10 11 12

[gak67]

=IF(MONTH(A1)=MONTH(B1),MONTH(A1) means that if the dates are both in the same month the result is that month

IF(MONTH(EDATE(A1,1))=MONTH(B1) is checking if the end date is in the month following the start date (if the start and end dates are in different months)

IF(EOMONTH(A1,0)+1-A1>B1-EOMONTH(B1,-1),MONTH(A1),MONTH(B1)) compares the number of days in each month (if there's not a full calendar month between them) and picks the month with the greater number of days. If the number of days is the same it chooses the month of the end date. If you want it to choose the month of the start date change it to IF(EOMONTH(A1,0)+1-A1>=B1-EOMONTH(B1,-1),MONTH(A1),MONTH(B1)).

MONTH(EDATE(A,1)) returns the month after the start date month (if there is a full calendar month between the start and end dates)

[Verbamore]

I question your logic on some of your dates.

Row 4 for instance, 17 Dec to 31 Dec is 15 days (inclusive), while 1 Jan to 14 Jan is only 14 days (inclusive), yet you say it should be Jan (1). Based on your logic, which seems to be ignoring the first day of the range, try this formula:

Please Login or Register  to view this content.

From Dec 17 to Dec 31 are 14 days .
I really appreciate it if you explain to me the formula above

[Verbamore]

=IF(MONTH(A1)=MONTH(B1),MONTH(A1) means that if the dates are both in the same month the result is that month

IF(MONTH(EDATE(A1,1))=MONTH(B1) is checking if the end date is in the month following the start date (if the start and end dates are in different months)

IF(EOMONTH(A1,0)+1-A1>B1-EOMONTH(B1,-1),MONTH(A1),MONTH(B1)) compares the number of days in each month (if there's not a full calendar month between them) and picks the month with the greater number of days. If the number of days is the same it chooses the month of the end date. If you want it to choose the month of the start date change it to IF(EOMONTH(A1,0)+1-A1>=B1-EOMONTH(B1,-1),MONTH(A1),MONTH(B1)).

MONTH(EDATE(A,1)) returns the month after the start date month (if there is a full calendar month between the start and end dates)

Thanks a lot gak67!

[gak67]

if you apply this two formula for example in one case like this(12/17/2013 - 1/14/2014) you'll see two different results.

I did and got the same result with both formulas, 12.

I would go with AlKey's formula. It's nice and simple.

[gak67]

From Dec 17 to Dec 31 are 14 days .

If you worked 7 days a week and went on holiday from 17 Dec to 31 Dec you would need to take 15 days leave, not 14, although you would be away for 14 nights. If you want to ignore the first day try this formula:

Please Login or Register  to view this content.

[AlKey]

Ok, Here it is!

in E2 and copy down

=MONTH(MEDIAN(A2+1,B2+1))

[Verbamore]

Ok, Here it is!

in E2 and copy down

=MONTH(MEDIAN(A2+1,B2+1))

Thanks AlKey for your help

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).