Format Result Of Date Difference To 2 Decimal Places

**Reafidy** · Apr 27th 2007

Hi All,

I need a VBA solution only please.

I use datediff to calculate the difference between 2 dates in Months.

Result = Datediff("M", Date1,Date2)

I need the result to be formatted to 2 decimal places ie 23.52 (months)

Not neccessary to use datediff if this doesnt work.

Please note I have cross posted this HERE because I couldnt get connected to ozgrid for some reason, I will montior both sites for a solution and post back results. Apologies for the inconvenience.

As always any help much appreciated and thanks in advance.

**Trebor76** · Apr 27th 2007

Re: Format Result Of Date Diff To 2 Decimal Places

Hi there,

Have a look at the following http://www.cpearson.com/excel/datedif.htm (particularly the Age UDF) which if doesn't immediately solve the issue, hopefully provides the foundation to do so.

HTH

Robert
: D

Gizzmo · Apr 27th 2007

Re: Format Result Of Date Diff To 2 Decimal Places

Hi Reafidy,

See attached file, I'm no expert but hopefully the attached function which I have amended will help you.

Cheers,

Gizzmo

**Reafidy** · Apr 27th 2007

Re: Format Result Of Date Diff To 2 Decimal Places

Thanks guys for all your help, from your replys I have developed the following:

Code

Sub MonthsBetween()
    Dim M As Integer
    Dim D As Integer
    Dim DaysInMonth As Integer
    Dim MonthDiff As String
    
    Dim Date1 As Date
    Dim Date2 As Date
    
    Date1 = "01/02/03"
    Date2 = "01/03/06"
    
    M = DateDiff("M", Date1, Date2)
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(Date2), Month(Date2), 0)) + D
    End If
    DaysInMonth = Day(DateAdd("d", -1, DateSerial(Year(Date2), Month(Date2) + 1, 1)))
    MonthDiff = M & Format(D / DaysInMonth, ".0#")
    MsgBox MonthDiff    
End Sub

Display More

Its part of a function in my spreadsheet and a little differnent but this is the jst of it.

Thanks for your help, if anyone can see flaws please let me know...

mudraker · Apr 27th 2007

Re: Format Result Of Date Difference To 2 Decimal Places

this function is the same as Gizzmo posted with a modification that takes into account the number of days in the month of date 2 similar to what Reafidy has already posted. but as I was working on it when he replied I thought I will still add my 2 cents worth

Code

Function DateDiff(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer


M = ((Year(Date2) - Year(Date1)) * 12) + (Month(Date2) - Month(Date1))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
DateDiff = M & Format(D / Day(DateSerial(Year(Date2), Month(Date2) + 1, "01") - 1), ".0#")
End Function

Display More

**Reafidy** · Apr 27th 2007

Re: Format Result Of Date Difference To 2 Decimal Places

Thanks Mudraker for your help. I appreciate it. I think we are all thinking along the same lines.

rajsanghani_200 · Mar 14th 2017

Re: Format Result Of Date Diff To 2 Decimal Places

Thanx... Gizzmo... your solution did help..

Format Result Of Date Difference To 2 Decimal Places

Participate now!

Share

Users Viewing This Thread