Date to Date Calculation in Months

I want to calculate the total months between a date "10/10/2007" and today's date using the TODAY() argument and I want to use one cell. That is I don't want to use extra cells for 10/10/2007, TODAY(), and the cell with a formula, which is currently "=(YEAR(D3)-YEAR(C3))*12+MONTH(D3)-MONTH(C3)".

I've tried using the "=DATEDIF(10/10/2007,TODAY(),"m")" formula, which returns a calculation of 1370, which I think is a date of 10/01/1903. How do I get the formula to calculate correctly? The calculation should actually be "77" which is the total months between 10/10/2007 and today! Thank you...

Here is the formula: =DATEDIF("10 Oct 2007",TODAY(),"m")

The formula will take your 10/10/2007 and interpret that as meaning 10 divided by 10 and then divided by 2007, which equates to a very small date.

To avoid that you can write your formula like this:

=DATEDIF(--"10/10/2007",TODAY(),"m")"

or like this:

=DATEDIF(DATE(2007,10,10),TODAY(),"m")

Hope this helps.

Pete

Originally Posted by jamezam

I want to calculate the total months between a date "10/10/2007" and today's date using the TODAY() argument and I want to use one cell. That is I don't want to use extra cells for 10/10/2007, TODAY(), and the cell with a formula, which is currently "=(YEAR(D3)-YEAR(C3))*12+MONTH(D3)-MONTH(C3)".

I've tried using the "=DATEDIF(10/10/2007,TODAY(),"m")" formula, which returns a calculation of 1370, which I think is a date of 10/01/1903. How do I get the formula to calculate correctly? The calculation should actually be "77" which is the total months between 10/10/2007 and today! Thank you...

=DATEDIF("10/10/2007",TODAY(),"m")

For the date 10/10/2007, it is performing a calculation, 10 divided by 10 = 1, divided by 2007 = 0.00049826

So, you need to put the date in double quotes so it is treated as text, and in this case it reads it as a date

Originally Posted by Richard Buttrey

Hi,

The

Formula:

=DATEDIF(A1,TODAY(),"m")

function works for me. Are you able to share your workbook with us?

The reason it works for you is that it is treating the value in A1 as a date, OP didn't want to use additional columns

For you to replicate what the OP was seeing you would have to enter =10/10/2007 into your A1 cell, rather than just 10/10/2007