paste this code into a module,
then usage is:
sElapsed = CalcElapsedTimeAsTxt(txtDate1, txtDate2)
Code:
Public Function CalcElapsedTimeAsTxt(ByVal pvDate1, ByVal pvDate2)
Dim lSecs As Long
lSecs = DateDiff("s", pvDate1, pvDate2)
CalcElapsedTimeAsTxt = ElapsedTimeAsTextRecur(lSecs)
End Function
'USAGE: ElapsedTimeAsTextRecur(655)
Public Function ElapsedTimeAsTextRecur(ByVal pvSecs, Optional ByVal pvSecBlock)
'recursive time lapse given seconds
Dim vTxt, sUnit
Dim iNum As Long
Const kDAY = 86400
Const kSECpYR = 31536000
'60 sec = 1 min 60 sec
'60 min = 1 hr 3600 sec
'24 hr = 1 day 86400 sec
'7 days = 1 week 604800 sec
'30 days = 1 month 2592000
'12 months = 1 year = 31536000
'YEARS
If IsMissing(pvSecBlock) Then pvSecBlock = kSECpYR
iNum = pvSecs \ pvSecBlock
Select Case pvSecBlock
Case kSECpYR 'yr
sUnit = "year"
If iNum > 0 Then
vTxt = vTxt & iNum & " " & sUnit
GoSub FixPlural
pvSecs = pvSecs - (iNum * pvSecBlock)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 2592000)
Case 2592000 'MO
sUnit = "month"
If iNum > 0 Then
If iNum > 11 Then iNum = 11
vTxt = vTxt & iNum & " " & sUnit
GoSub FixPlural
pvSecs = pvSecs - (iNum * pvSecBlock)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 604800)
Case 604800 'WEEK
sUnit = "week"
If iNum > 0 Then
If iNum > 3 Then iNum = 3
vTxt = vTxt & iNum & " " & sUnit
GoSub FixPlural
pvSecs = pvSecs - (iNum * kDAY * 7)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 86400)
Case kDAY 'day
sUnit = "day"
If iNum > 0 Then
vTxt = vTxt & iNum & " " & sUnit
GoSub FixPlural
pvSecs = pvSecs - (iNum * kDAY)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 3600)
Case 3600 'hrs
sUnit = "hr"
If iNum > 23 Then iNum = 23
If iNum > 0 Then
vTxt = vTxt & iNum & " " & sUnit
GoSub FixPlural
pvSecs = pvSecs - (iNum * pvSecBlock)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 60)
Case 60 'min
sUnit = "min"
If iNum > 0 Then
vTxt = vTxt & iNum & " min "
pvSecs = pvSecs - (iNum * pvSecBlock)
End If
vTxt = vTxt & ElapsedTimeAsTextRecur(pvSecs, 1)
Case Else
sUnit = "secs"
'If pvSecs > 0 Then vTxt = vTxt & pvSecs & " seconds"
End Select
ElapsedTimeAsTextRecur = vTxt
Exit Function
FixPlural:
If iNum > 1 Then vTxt = vTxt & "s " Else: vTxt = vTxt & " "
Return
End Function