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:

[TABLE="class: grid, width: 304"]
<tbody>[TR]
[TD="align: center"]C/R
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Units[/TD]
[TD="align: center"]DP[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1.75[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]0[/TD]
[TD]2 secs
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1.75[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]1[/TD]
[TD]1.8 secs[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]59.49[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]1[/TD]
[TD]59.5 secs[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]59.50[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]1[/TD]
[TD]59.5 secs[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]59.50[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]0[/TD]
[TD]1 mins[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]59.95[/TD]
[TD="align: center"]sec
[/TD]
[TD="align: center"]2[/TD]
[TD]59.95 secs[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]59.95[/TD]
[TD="align: center"]sec[/TD]
[TD="align: center"]1[/TD]
[TD]1 mins[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]23.49[/TD]
[TD="align: center"]hour[/TD]
[TD="align: center"]1[/TD]
[TD]23.5 hours[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]23.49[/TD]
[TD="align: center"]hour[/TD]
[TD="align: center"]0[/TD]
[TD]23 hours[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]23.50[/TD]
[TD="align: center"]hour[/TD]
[TD="align: center"]1[/TD]
[TD]23.5 hours[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]23.50[/TD]
[TD="align: center"]hour[/TD]
[TD="align: center"]0[/TD]
[TD]1 days[/TD]
[/TR]
</tbody>[/TABLE]


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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Time​

[/td][td="bgcolor:#F3F3F3"]

Formatted​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0:00:00.332​

[/td][td="bgcolor:#E5E5E5"]

0.332s​

[/td][td="bgcolor:#E5E5E5"]B2: =jm(A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

0:00:01.006​

[/td][td="bgcolor:#E5E5E5"]

0.017m​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

0:00:58.535​

[/td][td="bgcolor:#E5E5E5"]

0.976m​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

0:01:11.296​

[/td][td="bgcolor:#E5E5E5"]

0.020hr​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

0:46:32.931​

[/td][td="bgcolor:#E5E5E5"]

0.776hr​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1:02:32.439​

[/td][td="bgcolor:#E5E5E5"]

0.043d​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

22:39:55.710​

[/td][td="bgcolor:#E5E5E5"]

0.944d​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

26:10:18.067​

[/td][td="bgcolor:#E5E5E5"]

0.156wk​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

163:24:33.349​

[/td][td="bgcolor:#E5E5E5"]

0.973wk​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

179:15:14.490​

[/td][td="bgcolor:#E5E5E5"]

0.249mo​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

191:08:08.762​

[/td][td="bgcolor:#E5E5E5"]

0.265mo​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

211:10:45.960​

[/td][td="bgcolor:#E5E5E5"]

0.293mo​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

790:18:56.000​

[/td][td="bgcolor:#E5E5E5"]

0.090yr​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

8731:26:45.000​

[/td][td="bgcolor:#E5E5E5"]

0.997yr​

[/td][td][/td][/tr]
[/table]

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