Date to Date Calculation in Months

[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...

[RudiS]

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

[bebo021999]

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

[Pete_UK]

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

[ThirtyTwo]

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...

Please Login or Register  to view this content.

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

[Richard Buttrey]

Hi,

The

Please Login or Register  to view this content.

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

[ThirtyTwo]

Hi,

The

Please Login or Register  to view this content.

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

[Richard Buttrey]

@thirtytwo

Indeed so. It just strikes me as odd when anyone wants to hard code values in formulae (or macros for that matter), when it seems no hardship to use a cell somewhere that carries the variable / constant.