Tuesday, January 3, 2012

How to Calculate Date Difference :

Try this Function
if A1 is Project Start date and A2 is  Project End Data . You can calculate aging between them in Year,Month and date :
Use this in A3
=DatedDiff(A1,A2)



Function DatedDiff(dtStart As Date, dtEnd As Date) As String
   
    Dim ArrStart
    Dim ArrEnd
    Dim ArrResult
   
    ArrStart = Array(Year(dtStart), Month(dtStart), Day(dtStart))
    ArrEnd = Array(Year(dtEnd), Month(dtEnd), Day(dtEnd))
    ArrResult = Array(0, 0, 0)
    If ArrEnd(2) < ArrStart(2) Then
        ArrEnd(2) = ArrEnd(2) + Day(CDate(ArrEnd(1) & "-1-" & ArrEnd(0)) - 1)
        ArrEnd(1) = ArrEnd(1) - 1
    End If
    ArrResult(2) = ArrEnd(2) - ArrStart(2)
    If ArrEnd(1) < ArrStart(1) Then
        ArrEnd(1) = ArrEnd(1) + 12
        ArrEnd(0) = ArrEnd(0) - 1
    End If
    ArrResult(1) = ArrEnd(1) - ArrStart(1)
    ArrResult(0) = ArrEnd(0) - ArrStart(0)
    If dtEnd < dtStart Then
        DatedDiff = "StartDate>EndData=TRUE"
    Else
        DatedDiff = "Year= " & ArrResult(0) & " | Month= " & ArrResult(1) & " | Day= " & ArrResult(2)
    End If
    Erase ArrStart
    Erase ArrEnd
    Erase ArrResult
End Function

No comments: