?
Solved

difference between 2 dates in excel

Posted on 2009-04-03
11
Medium Priority
?
614 Views
Last Modified: 2012-05-06
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
Comment
Question by:sassy168
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24061830
=DAYS360(F4,NOW(),30) in excel
0
 

Author Comment

by:sassy168
ID: 24061863
that didn't work
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24061866
the above will give assuming 360 days in a year
use the following
use =(YEAR(F4)-YEAR(Now()))*12+MONTH(F4)-MONTH(Now())
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 2000 total points
ID: 24061878
Use this formula...it will do what you are looking for...
=DATEDIF(F4,TODAY(),"ym")
Saurabh...
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 24061889
enclosed is the workbook for your reference which tells how datedif works...
Saurabh...

datedif.xls
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 24062159
I would use ...

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

Good luck,

sdwalker
0
 
LVL 12

Expert Comment

by:sdwalker
ID: 24062168
or in your particular case ....

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

Expert Comment

by:barry houdini
ID: 24064304
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

by:sdwalker
ID: 24066968
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

by:hnasr
ID: 24070923
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

by:sassy168
ID: 31566334
that worked, thank you
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question