# difference between 2 dates in excel

i would like to calculate the difference between two dates in months, and this doesn't work.
=DAYDIF(F4,NOW(), "m")
###### Who is Participating?

Commented:
Use this formula...it will do what you are looking for...
=DATEDIF(F4,TODAY(),"ym")
Saurabh...
0

=DAYS360(F4,NOW(),30) in excel
0

Author Commented:
that didn't work
0

the above will give assuming 360 days in a year
use the following
use =(YEAR(F4)-YEAR(Now()))*12+MONTH(F4)-MONTH(Now())
0

Commented:
enclosed is the workbook for your reference which tells how datedif works...
Saurabh...

datedif.xls
0

Commented:
I would use ...

=(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3)

Good luck,

sdwalker
0

Commented:
or in your particular case ....

=(YEAR(now())-YEAR(F4))*12+MONTH(now())-MONTH(F4)
0

Commented:
Hello saay168,
You need to define exactly how you want to count months, I think. All the following use Excel.
sdwalker's suggestion gives a count which doesn't depend on the day of the month. This month to next month will be 1, whatever the days so if F4 is 1st March 2009 and today is 30th April you'll get 1 and you'll also get 1 for the date period 31st March to 1st April 2009.
You can get exactly the same result with this formula
=DATEDIF(F4-DAY(F4)+1,TODAY(),"m")
or do you want to count only whole months that have passed......so 1 month and 27 days will just return 1, that's a straightforward DATEDIF, i.e.
=DATEDIF(F4,TODAY(),"m")
or round to the nearest month.........
=DATEDIF(F4,TODAY()+15,"m")
regards, barry

0

Commented:
One other way is to just assume 30 days for a month and use ...

=Round((Now()-F4)/30,0)

Good luck,

sdwalker
0

Commented:
Use VBA, try with a new worrkbook

Alt+F11 to start editor
Double click Sheet1

Type:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Cells("2", "A") = DateDiff("m", Me.Cells("1", "A"), Now())
End Sub
0

Author Commented:
that worked, thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.