Solved

difference between 2 dates in excel

Posted on 2009-04-03
11
600 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

785 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