Month Difference formula.

[Guest]

Hi,

Could someone please help me with the formula below.

I am trying to write a formula that will give the difference between a
future expiry date and NOW() in Months and Days in a single cell formula.
The formula I have come up with so far produces the difference in Weeks and
Days (eg: "4 Weeks And 6 Days"), but I can't work out how to do the same
formula in Months and days (eg "3 Months And 4 Days"). (Cell B1 contains the
'Expiry Date').

=CONCATENATE(INT((B1-NOW())/7)," Weeks and ",MOD(INT(B1-NOW()),7)," Days")

Thank you in advance for any help anyone can offer.

 

[Harald Staff]

Hi

Here's a start; difference in months with the later date in B1:

=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

HTH. Best wishes Harald

 

[Bernd Plumhoff]

See DATEDIF(). Use the Analysis Toolpack.

HTH,
Bernd

 

[Dave Peterson]

I don't think =datedif() is in the analysis toolpak for current versions.

The OP can find lots of information about =datedif() at Chip Pearson's site:
http://www.cpearson.com/excel/datedif.htm

 

[Guest]

Thank you for all your help (and directions!).