in [Programming]

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 |