Prev: Copy the contents of a range of cells to a single comment (indicator)?
Next: Search within a single cell for a character
From: Prof Wonmug on 29 Apr 2010 19:56
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
From: Gary Brown on 30 Apr 2010 10:28
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
> .
>
From: Prof Wonmug on 30 Apr 2010 11:43
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
 | 
Pages: 1
Prev: Copy the contents of a range of cells to a single comment (indicator)?
Next: Search within a single cell for a character