Solved

difference between 2 dates in excel

Posted on 2009-04-03
11
596 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
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 12

Expert Comment

by:udayakumarlm
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:udayakumarlm
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
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now