On Sat, 23 Jun 2007 06:51:00 -0700, Gary''s Student
wrote:

No reason to be sorry (but there is stilll a problem).

Damn!

I examined your function, putting test values in column A and the function
call in Column B:

99999 273.8Y
0.01 14.4M
0.9 21.6H
0.99 1.0D
500 1.4Y
1000 2.7Y

Note that .99 results in 1 Day rather than 23.8 Hours

Good catch. I was testing the values backwards.

Here's a complete rewrite (also replacing years with weeks):

Public Function FmtInt(ByVal interval As Double) As String

Const TSWeek As Double = 7 ' 1 week in days
Const TSDay As Double = 1 ' 1 day in days
Const TSHour As Double = TSDay / 24 ' 1 hour in days
Const TSMin As Double = TSHour / 60 ' 1 minute in days
Const TSSec As Double = TSMin / 60 ' 1 second in days
Const FmtPat As String = "0.0" ' The format function pattern

If Format(interval / TSSec, FmtPat) < 60 Then 'If < 60 seconds
(1 minute),
FmtInt = Format(interval / TSSec, FmtPat) & "S" 'Format in
seconds
ElseIf Format(interval / TSMin, FmtPat) < 60 Then 'If < 60 minutes
(1 hour),
FmtInt = Format(interval / TSMin, FmtPat) & "M" 'Format in
minutes
ElseIf Format(interval / TSHour, FmtPat) < 24 Then 'If < 24 hours
(1 day),
FmtInt = Format(interval / TSHour, FmtPat) & "H" 'Format in
hours
ElseIf Format(interval, FmtPat) < 7 Then 'If < 7 days (1
weeek),
FmtInt = Format(interval, FmtPat) & "D" 'Format in
days
Else 'Otherwise,
FmtInt = Format(interval / TSWeek, FmtPat) & "W" 'Format in
weeks
End If

End Function


I'll study your Event Code later.

Thanks for the help and for doing my debugging for me ;-)

Hey, it works for Microsoft!

--