Calculate the number of 1st of the months

J

jorismoerings

Well-known Member

Joined

Jul 4, 2014

Messages

1,523

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows

• Jul 16, 2017

• #2

Hi,

Try this:

Book1
	A	B	C	D	E
1	January 31, 2017 to March 2, 2017 it should output 2		31-1-2017	2-3-2017	2
2	February 2, 2017 to February 28, 2017 should output 0		2-2-2017	28-2-2017	0
3	January 1, 2017 to February 1, 2017 should also output 1, (I know this could look like two, which is why I wanted to point it out)		1-1-2017	1-2-2017	1
Sheet1

Cell Formulas
Range	Formula
E1	=MONTH(IF(DAY(D1)>1,EOMONTH(D1,0),D1))-MONTH(IF(DAY(C1)>1,EOMONTH(C1,0),C1))

 

Upvote 0

Tetra201

MrExcel MVP

Joined

Oct 14, 2016

Messages

4,037

• Jul 16, 2017

• #3

Try this:

=SUMPRODUCT(--(DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))=1))-(DAY(A1)+DAY(B1)=2)

 

Upvote 0

Tetra201

MrExcel MVP

Joined

Oct 14, 2016

Messages

4,037

• Jul 16, 2017

• #4

@jorismoerings:

Just to mention: for your formula to work, both dates must be in the same year.

 

Upvote 0

J

jorismoerings

Well-known Member

Joined

Jul 4, 2014

Messages

1,523

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows

• Jul 16, 2017

• #5

@Tetra201:
Correct and aware. Not completely sure what OP means with

the number of 1st of the months between them

Click to expand...

Cause given the statement your formula returns same result as mine while the OP quote could mean he wants the actual month number of the first month between.
Than both our formula's return an incorrect answer.
Let see what comes back.

 

Upvote 0

Tetra201

MrExcel MVP

Joined

Oct 14, 2016

Messages

4,037

• Jul 16, 2017

• #6

@jorismoerings:

My understanding of the "number of 1st of the months" between two dates is that, for example, Dec 25, 2016 to Jan 5, 2017 should return 1.

 

Upvote 0

J

jorismoerings

Well-known Member

Joined

Jul 4, 2014

Messages

1,523

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows

• Jul 16, 2017

• #7

@Tetra201:

I understand but change dates to Jan 31,2017 and Sep 2, 2017 and both formulas will return 8 while the the first month in between is February hence the number of the first month would be 2.
We're assuming our understanding is correct so that's why: let's wait and see.

 

Upvote 0

C

ClimbGuy

New Member

Joined

Jul 15, 2017

Messages

4

• Jul 16, 2017

• #8

Hi Thank you for all the help!

To clarify I want to know the number of times there is a 1st of the month between two dates. There will be times when the dates span one more more calendar years.

Nov 31, 2016-Feb 1, 2017 should return 3

If the two dates are Jan 31, 2017 and Sept 2, 2017 the formula should return 8 since there are 8 1st of the months between those two dates (Feb 1, Mar 1, April 1, May 1, June 1, July 1, Aug 1, & Sept 1)

 

Upvote 0

Tetra201

MrExcel MVP

Joined

Oct 14, 2016

Messages

4,037

• Jul 16, 2017

• #9

ClimbGuy said:

...Nov 30, 2016-Feb 1, 2017 should return 3

If the two dates are Jan 31, 2017 and Sept 2, 2017 the formula should return 8...

Click to expand...

That's exactly what my formula from Post #3 does. Have you tried it?

 

Upvote 0

C

ClimbGuy

New Member

Joined

Jul 15, 2017

Messages

4

• Jul 16, 2017

• #10

Tetra201 said:

That's exactly what my formula from Post #3 does. Have you tried it?

Click to expand...

you are right only 30 days in November.

in terms of your formula =SUMPRODUCT(--(DAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)))=1))-(DAY(A1)+DAY(B1)=2)

I assume A1=date 1 and B1= date 2?

 

Upvote 0