Calculate number of months in Excel

Ted Penner
Ted Penner used Ask the Experts™
on
How do I represent today less the value of cell in terms of number of months?

The below example shows my attempt
https://www.screencast.com/t/XoVirpBqlnn
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
You can try below:
=MONTH(TODAY())-MONTH(F19)

Open in new window

Jeff DarlingDeveloper Analyst
Commented:
=DATEDIF(NOW(),F19,"M")

Open in new window

Ted PennerSoftware Engineer
Commented:
Thank you both.

With =MONTH(TODAY())-MONTH(F19), I would end up with 3 months which is not accurate.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ted PennerSoftware Engineer

Author

Commented:
Jeff,

Your solution appears more solid but I still get an error in the top cell.

https://www.screencast.com/t/7Z3RufemZb
Jeff DarlingDeveloper Analyst
Commented:
It seems that DATEDIF doesn't like to return a negative.  so first test if greater and multiply by -1

=IF(F19>NOW(),DATEDIF(NOW(),F19,"M")*-1,DATEDIF(NOW(),F19,"M"))

Open in new window

ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Jeff is right, your specified date are more than today's date, try below:
=IF(DAY(NOW())>=DAY(F19),0,)+(YEAR(NOW())-YEAR(F19)) *12+MONTH(NOW())-MONTH(F19)

Open in new window

Ted PennerSoftware Engineer

Author

Commented:
Number should always be positive and if not a positive number of months, then the value should be zero.
Jeff DarlingDeveloper Analyst
Commented:
=IF(F19>NOW(),0,DATEDIF(NOW(),F19,"M"))

Open in new window

Managing Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
If its gonna be future dates in F column then try below:
=IF(DAY(NOW())>=DAY(F19),0,0)+(YEAR(F19)-YEAR(NOW())) *12+MONTH(F19)-MONTH(NOW())

Open in new window

Ted PennerSoftware Engineer

Author

Commented:
I think the values should be 9 and 21 if not past the expiration date as shown here.

https://docs.google.com/spreadsheets/d/1cjS8qfytLJwIwbId5xfNmHm5NxsX_9gwWYxbO_B55AA/edit?usp=sharing
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
With my last provided solution, you are getting 9 & 21
Month Calculation
Ted PennerSoftware Engineer

Author

Commented:
Thank you!!
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Please to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial