[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

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")

Open in new window

0
sassy168
Asked:
sassy168
  • 3
  • 2
  • 2
  • +3
1 Solution
 
udaya kumar laligondlaTechnical LeadCommented:
=DAYS360(F4,NOW(),30) in excel
0
 
sassy168Author Commented:
that didn't work
0
 
udaya kumar laligondlaTechnical LeadCommented:
the above will give assuming 360 days in a year
use the following
use =(YEAR(F4)-YEAR(Now()))*12+MONTH(F4)-MONTH(Now())
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saurabh Singh TeotiaCommented:
Use this formula...it will do what you are looking for...
=DATEDIF(F4,TODAY(),"ym")
Saurabh...
0
 
Saurabh Singh TeotiaCommented:
enclosed is the workbook for your reference which tells how datedif works...
Saurabh...

datedif.xls
0
 
sdwalkerCommented:
I would use ...

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

Good luck,

sdwalker
0
 
sdwalkerCommented:
or in your particular case ....

=(YEAR(now())-YEAR(F4))*12+MONTH(now())-MONTH(F4)
0
 
barry houdiniCommented:
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
 
sdwalkerCommented:
One other way is to just assume 30 days for a month and use ...

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

Good luck,

sdwalker
0
 
hnasrCommented:
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
 
sassy168Author Commented:
that worked, thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now