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
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:
Post a Comment