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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
"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")
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top