I have a bunch of data representing time intervals that come from a
database application. The intervals vary from a few seconds to several
years. The database (MySQL) stores time intervals as fixed point
numbers in units of seconds.

Reading the values in seconds is unnatural (a year is ~31.5E6
seconds), so I wrote a little UDF to convert the seconds to more
natural units. The conversion is:

Value Units
<60 seconds Seconds
<60 minutes Minutes
<24 hours Hours
<99 days Days
Otherwise Years

I chose 99 days as the threshold between days and years to keep it to
2 places to the left of the decimal point.

Here's the UDF. I'd appreciate any critiques or suggestions.


Public Function FmtTime(ByVal TimeVal As Double) As String

Const dp As Byte = 1
Const SecsPerMin As Long = 60 'seconds/minute
Const MinsPerHour As Long = 60 'minutes/hour
Const HoursPerDay As Long = 24 'hours/day
Const DaysPerYear As Long = 365 'days/year

Dim TimeVar As Double 'The value that gets adjusted
Dim TimeRound As Double 'The rounded value

TimeVar = TimeVal 'Start as seconds
TimeRound = Round(TimeVar, dp) 'Round to specified decimal places
If TimeRound < SecsPerMin Then 'If < 60, do it in seconds
FmtTime = FormatNumber(TimeRound, dp) & " Sec"
Exit Function
End If

TimeVar = TimeVar / SecsPerMin 'Convert to minutes
TimeRound = Round(TimeVar, dp)
If TimeRound < MinsPerHour Then 'If < 60, do it in minutes
FmtTime = FormatNumber(TimeRound, dp) & " Min"
Exit Function
End If

TimeVar = TimeVar / MinsPerHour 'Convert to hours
TimeRound = Round(TimeVar, dp)
If TimeRound < HoursPerDay Then 'If < 24, do it in hours
FmtTime = FormatNumber(TimeRound, dp) & " Hrs"
Exit Function
End If

TimeVar = TimeVar / HoursPerDay 'Convert to days
TimeRound = Round(TimeVar, dp)
If TimeRound < 99 Then 'If < 99, do it in days
FmtTime = FormatNumber(TimeRound, dp) & " Dys"
Exit Function
End If

TimeVar = TimeVar / DaysPerYear 'Convert to years
FmtTime = FormatNumber(TimeVar, dp) & " Yrs"

End Function


Here's some sample data

Seconds Units
0.0000 0.0 Sec
1.0000 1.0 Sec
59.0000 59.0 Sec
59.9000 59.9 Sec
59.9490 59.9 Sec
59.9950 1.0 Min
3,594.0000 59.9 Min
3,596.9400 59.9 Min
3,596.9999 59.9 Min
3,597.0000 1.0 Hrs
82,800.0000 23.0 Hrs
86,040.0000 23.9 Hrs
86,219.6400 23.9 Hrs
86,219.9999 23.9 Hrs
86,220.0000 1.0 Dys
4,320,000.0000 50.0 Dys
8,467,200.0000 98.0 Dys
8,544,960.0000 98.9 Dys
8,549,279.1360 98.9 Dys
8,549,280.0000 0.3 Yrs
31,536,000.0000 1.0 Yrs
33,081,264.0000 1.0 Yrs
157,680,000.0000 5.0 Yrs

Thanks

Looks fine.
You could use Select Case to make it a little more readable but your
function is AOK.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Prof Wonmug" wrote:

I have a bunch of data representing time intervals that come from a
database application. The intervals vary from a few seconds to several
years. The database (MySQL) stores time intervals as fixed point
numbers in units of seconds.

Reading the values in seconds is unnatural (a year is ~31.5E6
seconds), so I wrote a little UDF to convert the seconds to more
natural units. The conversion is:

Value Units
<60 seconds Seconds
<60 minutes Minutes
<24 hours Hours
<99 days Days
Otherwise Years

I chose 99 days as the threshold between days and years to keep it to
2 places to the left of the decimal point.

Here's the UDF. I'd appreciate any critiques or suggestions.


Public Function FmtTime(ByVal TimeVal As Double) As String

Const dp As Byte = 1
Const SecsPerMin As Long = 60 'seconds/minute
Const MinsPerHour As Long = 60 'minutes/hour
Const HoursPerDay As Long = 24 'hours/day
Const DaysPerYear As Long = 365 'days/year

Dim TimeVar As Double 'The value that gets adjusted
Dim TimeRound As Double 'The rounded value

TimeVar = TimeVal 'Start as seconds
TimeRound = Round(TimeVar, dp) 'Round to specified decimal places
If TimeRound < SecsPerMin Then 'If < 60, do it in seconds
FmtTime = FormatNumber(TimeRound, dp) & " Sec"
Exit Function
End If

TimeVar = TimeVar / SecsPerMin 'Convert to minutes
TimeRound = Round(TimeVar, dp)
If TimeRound < MinsPerHour Then 'If < 60, do it in minutes
FmtTime = FormatNumber(TimeRound, dp) & " Min"
Exit Function
End If

TimeVar = TimeVar / MinsPerHour 'Convert to hours
TimeRound = Round(TimeVar, dp)
If TimeRound < HoursPerDay Then 'If < 24, do it in hours
FmtTime = FormatNumber(TimeRound, dp) & " Hrs"
Exit Function
End If

TimeVar = TimeVar / HoursPerDay 'Convert to days
TimeRound = Round(TimeVar, dp)
If TimeRound < 99 Then 'If < 99, do it in days
FmtTime = FormatNumber(TimeRound, dp) & " Dys"
Exit Function
End If

TimeVar = TimeVar / DaysPerYear 'Convert to years
FmtTime = FormatNumber(TimeVar, dp) & " Yrs"

End Function


Here's some sample data

Seconds Units
0.0000 0.0 Sec
1.0000 1.0 Sec
59.0000 59.0 Sec
59.9000 59.9 Sec
59.9490 59.9 Sec
59.9950 1.0 Min
3,594.0000 59.9 Min
3,596.9400 59.9 Min
3,596.9999 59.9 Min
3,597.0000 1.0 Hrs
82,800.0000 23.0 Hrs
86,040.0000 23.9 Hrs
86,219.6400 23.9 Hrs
86,219.9999 23.9 Hrs
86,220.0000 1.0 Dys
4,320,000.0000 50.0 Dys
8,467,200.0000 98.0 Dys
8,544,960.0000 98.9 Dys
8,549,279.1360 98.9 Dys
8,549,280.0000 0.3 Yrs
31,536,000.0000 1.0 Yrs
33,081,264.0000 1.0 Yrs
157,680,000.0000 5.0 Yrs

Thanks
.

On Fri, 30 Apr 2010 07:28:06 -0700, Gary Brown
<junk_at_kinneson_dot_com wrote:

Looks fine.
You could use Select Case to make it a little more readable but your
function is AOK.

I thought about that, but couldn't figure out a way to make it work.

One problem is that the compare operands keep changing.

Another is that the units conversions are progressive. I would have to
do 1 division in case 1, 2 in case 2, etc. I thought my way was more
efficient, if a few more lines of code.

If there were 50 tests, I'd put it all in an array and use a loop.

Thanks