Solved

# difference between 2 dates in excel

Posted on 2009-04-03
592 Views
i would like to calculate the difference between two dates in months, and this doesn't work.
``````=DAYDIF(F4,NOW(), "m")
``````
0
Question by:sassy168
• 3
• 2
• 2
• +3

LVL 12

Expert Comment

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

Author Comment

that didn't work
0

LVL 12

Expert Comment

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

LVL 59

Accepted Solution

Saurabh Singh Teotia earned 500 total points
Use this formula...it will do what you are looking for...
=DATEDIF(F4,TODAY(),"ym")
Saurabh...
0

LVL 59

Expert Comment

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

datedif.xls
0

LVL 12

Expert Comment

I would use ...

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

Good luck,

sdwalker
0

LVL 12

Expert Comment

or in your particular case ....

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

LVL 50

Expert Comment

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

LVL 12

Expert Comment

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

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

Good luck,

sdwalker
0

LVL 30

Expert Comment

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 Closing Comment

that worked, thank you
0

## Featured Post

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …