Number of missing months between two anniversary dates

tiger3p

New Member
Joined
Feb 24, 2006
Messages
22
Hello,

Used the following formula but the results I'm getting is not what I'm expecting.

=DATEDIF(A1,B1,"m")+DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))

For example:

Cell A1: 3/30/2018
Cell B1: 3/1/2019

The number of months missing should be 12, but I'm getting 10.06.

Cell A2: 12/10/2018
Cell B2: 3/1/2019

The number of months missing should be 2 months but the formula is giving me 1. Anyway to get around this?

Thanks in advance!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
Why are you adding this to the formula ??
DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))
Where did you get it from?
What's it supposed to achieve?

The number of complete months is simply
DATEDIF(A1,B1,"m")

3/20/2018 to 3/1/2019 - DATEDIF(A1,B1,"m") produces 11months on my Excel, 12 months has not occurred until 3/20/2019.
Babies born on 3/20/2018 don't have a birthday until 3/20/2019 therefore they are not yet 1 year old on 3/1/2019 so only 11 months have passed. The answer's 11.

12/10/2018 to 3/1/2019 - DATEDIF(A1,B1,"m") produces 2 months on my Excel
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))
is producing a negative number which is reducing your DATEDIF() calculation

1/30 - 30/31 =

-0.935483871
 
Last edited:

tiger3p

New Member
Joined
Feb 24, 2006
Messages
22
Why are you adding this to the formula ??
DAY(B1)/(32-DAY(B1-DAY(B1)+32))-DAY(A1)/(32-DAY(A1-DAY(A1)+32))
Where did you get it from?
What's it supposed to achieve?

The number of complete months is simply
DATEDIF(A1,B1,"m")

3/20/2018 to 3/1/2019 - DATEDIF(A1,B1,"m") produces 11months on my Excel, 12 months has not occurred until 3/20/2019.
Babies born on 3/20/2018 don't have a birthday until 3/20/2019 therefore they are not yet 1 year old on 3/1/2019 so only 11 months have passed. The answer's 11.

12/10/2018 to 3/1/2019 - DATEDIF(A1,B1,"m") produces 2 months on my Excel
Thanks for the reply! What if I just want the the difference in months absence of the day?
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,328
"What if I just want the the difference in months absence of the day?"

The number of complete months is simply
DATEDIF(A1,B1,"m")
 

Forum statistics

Threads
1,085,010
Messages
5,381,201
Members
401,718
Latest member
Ereese20

Some videos you may like

This Week's Hot Topics

Top