Is there a way to format time in variable units?

[JenniferMurphy]

I would like to be able to format a time value so that it is displayed in the smallest units that are less than "1" of the next larger unit. That is, use seconds up to 1 minute, then minutes up to 1 hour, hours up to 1 day, etc. I would also like to be able to specify the number of decimal points to display, which complicates things.

Here are some examples:

C/R	A	B	C	D
1	Value	Units	DP	Result
2	1.75	sec	0	2 secs
3	1.75	sec	1	1.8 secs
4	59.49	sec	1	59.5 secs
5	59.50	sec	1	59.5 secs
6	59.50	sec	0	1 mins
7	59.95	sec	2	59.95 secs
8	59.95	sec	1	1 mins
9	23.49	hour	1	23.5 hours
10	23.49	hour	0	23 hours
11	23.50	hour	1	23.5 hours
12	23.50	hour	0	1 days

<tbody>
</tbody>

I started writing a UDF, but it quickly got complicated. Is there an easy way to do this?

If not, I'll post my UDF if I ever get it working.

 

[mikeross]

Do you need week, month, years?

 

[JenniferMurphy]

For now, the times will be fairly short. Most will be less than a week. But I would like to generalize it as much as possible, so weeks, yes; months, probably; years, maybe.

PS: I hesitate on months and years because they are of variable length. But I would be willing to accept 30 days as a month and 365 days as a year.

 

[Rick Rothstein]

Would you ever have an entry like 31 hours?

 

[mikeross]

Would you ever have a situation like “160 seconds” or will it always be in the highest unit outside of rounding?

 

[mikerickson]

Why is 59.95 seconds with 1 decimal place 1 min rather than 1.0 min?

 

[JenniferMurphy]

I see that I was not very clear in my specs. Sorry about that.

Most of the time, the input values will be the result of subtracting two time stamps (endtime - starttime). Since Excel keeps time in units of days, those values will be in days, although the differences could be as little as a few seconds.

However, there are instances where the start and end times are in other units (mostly seconds). So, yes, I could have a value of 297 with units of seconds.

In the most general case, the value could be anything as could the units.

The UDF I am writing starts like this:

Public Function FmtTime(ByVal pTime As Double, _
                        Optional ByVal pInUnits As String = "Days", _
                        Optional ByVal pDP As Byte = 1, _
                        Optional ByVal pNegOK As Boolean = False) As String

pTime is the input value.

pInUnits is the units ("Secs", "Mins", etc.)

pDP is the number of decimal places to display in the result.

pNegOK is a switch that determines whether negative times are OK or generate a Value error.

Is that clearer?

 

[shg]

	A​	B​	C​
1​	Time​	Formatted​	​
2​	0:00:00.332​	0.332s​	B2: =jm(A2)
3​	0:00:01.006​	0.017m​
4​	0:00:58.535​	0.976m​
5​	0:01:11.296​	0.020hr​
6​	0:46:32.931​	0.776hr​
7​	1:02:32.439​	0.043d​
8​	22:39:55.710​	0.944d​
9​	26:10:18.067​	0.156wk​
10​	163:24:33.349​	0.973wk​
11​	179:15:14.490​	0.249mo​
12​	191:08:08.762​	0.265mo​
13​	211:10:45.960​	0.293mo​
14​	790:18:56.000​	0.090yr​
15​	8731:26:45.000​	0.997yr​

Function JM(t As Double) As String
  Select Case t
    Case Is < 0.9995 / 86400
      JM = Format(t * 86400, "0.000\s")
    Case Is < 0.9995 / 1440
      JM = Format(t * 1440, "0.000\m")
    Case Is < 0.9995 / 24
      JM = Format(t * 24, "0.000\h\r")
    Case Is < 0.9995 * 1
      JM = Format(t / 1, "0.000\d")
    Case Is < 0.9995 * 7
      JM = Format(t / 7, "0.000\w\k")
    Case Is < 0.9995 * 30
      JM = Format(t / 30, "0.000\mo")
    Case Else
      JM = Format(t / 365, "0.000\y\r")
  End Select
End Function

 

[shg]

Rereading your question, that doesn't format as you requested.

 

[mikerickson]

Perhaps

Function TimeConversion(ByVal Number As Double, Units As String, DP As Long) As String
    Units = LCase(Units)
    Dim NumSec As Double, FormatSuffix
    Dim outUnits As String
    
    Rem convert to seconds
    Select Case Units
        Case "sec"
            Rem do nothing
        Case "min"
            Number = Number * 60
        Case "hr", "hour"
            Number = Number * 60 * 60
    End Select
    
    Rem format to decimal place
    FormatSuffix = "." & String(DP, "0")
    TimeConversion = Format(Number, "0" & FormatSuffix)
    
    Rem select output units
    outUnits = " Sec"
    If TimeConversion Like "*" & FormatSuffix Then
        TimeConversion = Format(Val(TimeConversion) / 60, "0" & FormatSuffix)
        outUnits = " Min"
    End If
    If TimeConversion Like "*" & FormatSuffix Then
        TimeConversion = Format(Val(TimeConversion) / 60, "0" & FormatSuffix)
        outUnits = " Hr."
    End If
    TimeConversion = TimeConversion & outUnits
End Function