Excel advice please

[garystu1965]

Hi folks,

I have two dates in this format...

04/01/2010 08:20 and 11/05/2010 11:15

Need to work out the difference between them in minutes.

Then when I have got the minutes value work it back so it show its in months, days, minutes.

Crikey !!!

Any ideas bright sparks ?

Gary

 

[Dec]

...Online Conversion - Date/Time Difference

Dec
Edit , that site might depend on the format, is it 4 January 2010 or 1 April 2010

 

[davidjpowell]

I can get it to minutes, by subtracting one from the other and using the Custom code of [m]:mm:ss for format.

I can get number of days and decimal version of hours by just referncing the same cell and using Number Format. If you try and get it into days and months it's going to be hard to manage, not least as different months have different days, unless I suppose you have a fixed 'start' date.

 

[LTD]

If you want it to do both then create two different formulas for it in the formats you require. That's the simple way and will account automatically for differing month lengths.

 

[MOCAŠ]

As davidjpowell has said, getting the number of days is quite straightforward by just taking the first date from the second. In fact, you can display the result in 'minutes' without applying custom number formats, simply by multiplying the result by 1440 (number of minutes in a day).

Showing the difference in months, days and minutes is more of a challenge. You could achieve it with a series of convoluted formulae, but the cleanest way would be to write a custom function using Excel's VBA module.

[Alt]+[F11] will open the VB editor. Then from the editior's menu, select Insert | Module and paste the following code at the flashing cursor:

Function dateDiff(date1, date2)
 
    monthEnd = date1: endDate = date2
    time1 = date1 - Int(date1): time2 = date2 - Int(date2)
    If time2 < time1 Then endDate = endDate - 1: time2 = time2 + 1
    For i = 1 To 1000
        testDate = Application.WorksheetFunction.EDate(date1, i)
        If testDate > endDate Then monthCount = i - 1: Exit For
        monthEnd = testDate
    Next
    dayCount = Format(endDate - monthEnd, "0")
    minuteCount = Hour(time2 - time1) * 60 + Minute(time2 - time1)
    dateDiff = monthCount & "m, " & dayCount & "d, " & minuteCount & "mins"
 
End Function

You can then close the VB editor, and use your new dateDiff function within the spreadsheet.

So, let's say you enter your date/time values in cells A1 and A2 respectively. In cell A3 (or wherever) you can enter: =dateDiff(A1, A2)

and the result will be: 4m, 7d, 175mins for the sample dates you specified.

You can adjust the way the result is displayed, or even get the function to return the months, days and minutes as separate values if desired, by tweaking the code within the function.