Is there a way to format time in variable units?

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
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.
 
Sort by date Sort by votes
The following UDF returns the expected results for the dataset from Post # 1:
Code: 
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
    If pTime < 0 And Not pNegOK Then FmtTime = CVErr(xlErrValue): Exit Function
    prf = Sgn(pTime)
    pTime = Round(Abs(pTime), pDP)
    Select Case Left(UCase(pInUnits), 1)
        Case "S": curr = " secs": thr = 60: nxt = " mins"
        Case "M": curr = " mins": thr = 60: nxt = " hours"
        Case "H": curr = " hours": thr = 24: nxt = " days"
        Case "D": curr = " days": thr = 30: nxt = " months"
    End Select
    If pTime < thr Then
        FmtTime = prf * pTime & curr
    Else
        FmtTime = prf * Round(pTime / thr, pDP) & nxt
    End If
End Function
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
i tried using formula

Code: 
Value	Units	DP	Result       	using formula
1.75	sec	0	2 secs       	2 secs
1.75	sec	1	1.8 secs	1.8 secs
59.49	sec	1	59.5 secs	59.5 secs
59.5	sec	1	59.5 secs	59.5 secs
59.5	sec	0	1 mins       	1 mins
59.95	sec	2	59.95 secs	59.95 secs
59.95	sec	1	1 mins      	1 mins
23.49	hour	1	23.5 hours	23.5 hours
23.49	hour	0	23 hours	23 hours
23.5	hour	1	23.5 hours	23.5 hours
23.5	hour	0	1 days       	1 days
160.5	sec	1       		2.7 mins
200.5	sec	0      	        	3 mins
61.7	sec	1       		1 mins
12.6	hour	1              		12.6 hours
25.6	hour	1               	1.1 days
30.2	hour	1              		1.3 days
180.2	sec	1              		3 mins
98	sec	10	        	1.6333333333 mins
101.5	hour	5	         	4.22917 days
76.367	hour	17	        	3.18195833333333 days
42	hour	4	        	1.75 days
140.777	sec	2	        	2.35 mins


cell under "using formula" column is
Code: 
=IF(B2="sec",(IF(QUOTIENT(ROUND(A2,C2),60)<1, ROUND(A2,C2)&" secs",QUOTIENT(ROUNDUP(A2,C2),60)+IF(ROUND(MOD(A2,60)/60,C2)<1,ROUND(MOD(A2,60)/60,C2),0)&" mins")),IF(B2="min",IF(QUOTIENT(ROUND(A2,C2),60)<1, ROUND(A2,C2)&" mins",QUOTIENT(ROUNDUP(A2,C2),60)+IF(ROUND(MOD(A2,60)/60,C2)<1,ROUND(MOD(A2,60)/60,C2),0)&" hours"),IF(B2="hour",IF(QUOTIENT(ROUND(A2,C2),24)<1, ROUND(A2,C2)&" hours",QUOTIENT(ROUNDUP(A2,C2),24)+IF(ROUND(MOD(A2,24)/24,C2)<1,ROUND(MOD(A2,24)/24,C2),0)&" days"))))

this doesnt account for units in days though
 
Upvote 0
Thanks to everyone for their comments. Unless I made a mistake, the code form both Mike E and Tetra have bugs related to rounding. I believe the problem is that they round before converting the units. Based on all of this, I completed my UDF, which I have posted below. Here are the results of the sample data for the three solutions:

[TABLE="class: grid, width: 520"]
<tbody>[TR]
[TD="align: center"]R/C
[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/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]
[TD="align: center"]MikeE[/TD]
[TD="align: center"]Tetra[/TD]
[TD="align: center"]JM[/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="align: right"]2 secs
[/TD]
[TD="align: right"]0. Hr.[/TD]
[TD="align: right"]2 secs[/TD]
[TD="align: right"]2 sec[/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="align: right"]1.8 secs[/TD]
[TD="align: right"]1.8 Sec[/TD]
[TD="align: right"]1.8 secs[/TD]
[TD="align: right"]1.8 sec[/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="align: right"]59.5 secs[/TD]
[TD="align: right"]59.5 Sec[/TD]
[TD="align: right"]59.5 secs[/TD]
[TD="align: right"]59.5 sec
[/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="align: right"]59.5 secs[/TD]
[TD="align: right"]59.5 Sec[/TD]
[TD="align: right"]59.5 secs[/TD]
[TD="align: right"]59.5 sec[/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="align: right"]1 mins[/TD]
[TD="align: right"]0. Hr.[/TD]
[TD="align: right"]1 mins[/TD]
[TD="align: right"]1 min
[/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="align: right"]59.95 secs[/TD]
[TD="align: right"]59.95 Sec[/TD]
[TD="align: right"]59.95 secs[/TD]
[TD="align: right"]59.95 sec[/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="align: right"]1.0 mins[/TD]
[TD="align: right"]0.0 Hr.[/TD]
[TD="align: right"]1 mins[/TD]
[TD="align: right"]1.0 min[/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="align: right"]23.5 hours
[/TD]
[TD="align: right"]1409.4 Min[/TD]
[TD="align: right"]23.5 hours[/TD]
[TD="align: right"]23.5 hrs[/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="align: right"]23 hours[/TD]
[TD="align: right"]23. Hr.[/TD]
[TD="align: right"]23 hours[/TD]
[TD="align: right"]23 hrs[/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="align: right"]23.5 hours[/TD]
[TD="align: right"]23.5 Hr.[/TD]
[TD="align: right"]23.5 hours[/TD]
[TD="align: right"]23.5 hrs[/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="align: right"]1 days[/TD]
[TD="align: right"]24. Hr.[/TD]
[TD="align: right"]1 days[/TD]
[TD="align: right"]1 dys[/TD]
[/TR]
</tbody>[/TABLE]

Here's my code. Comments appreciated.

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

Const MaxSec As Double = 60   'Cutoff for converting over to minutes
Const MaxMin As Double = 60   'Cutoff for converting over to hours
Const MaxHrs As Double = 24   'Cutoff for converting over to days
Const MaxDys As Double = 14   'Cutoff for converting over to weeks
Const MaxWks As Double = 52   'Cutoff for converting over to years
Dim OutTime As Double         'The working time value
Dim temp As Double            'Temporary time variable

' Check for negative time
If pTime < 0 Then         'If the time is negative,
  If Not pNegOK Then        'And that is not OK,
    FmtTime = CVErr(xlErrValue) 'Return a Value error
    Exit Function
  End If
End If
OutTime = Abs(pTime)      'Convert to positive for formatting

'Convert everything to seconds
Select Case UCase(pInUnits)      'Process the pInUnits argument
  Case "S", "SEC", "SECS", "SECOND", "SECONDS"  'If in seconds, no conversion
  Case "M", "MIN", "MINS", "MINUTE", "MINUTES"  'If in minutes,
    OutTime = OutTime * 60                        'Convert to seconds
  Case "H", "HRS", "HOUR", "HOURS"              'If in hours,
    OutTime = OutTime * 60 * 60                   'Convert to seconds
  Case "D", "DYS", "DAY", "DAYS"                'If in days,
    OutTime = OutTime * 60 * 60 * 24              'Convert to seconds
  Case "W", "WKS", "WEEK", "WEEKS"              'If in weeks,
    OutTime = OutTime * SecsPerWeek               'Convert to seconds
  Case "Y", "YRS", "YEAR", "YEARS"              'If in years,
    OutTime = OutTime * SecsPerYear               'Convert to seconds
  Case Else                                     'If none on the above.
    FmtTime = CVErr(xlErrValue)                   'Return Value error
    Exit Function
End Select

'Will it be seconds?
temp = Round(OutTime, pDP)      'Round to specified decimal places
If temp < MaxSec Then           'If < max secs, do it in seconds
  FmtTime = FormatNumber(OutTime, pDP) & " sec"
  GoTo Done
End If

'Will it be minutes?
OutTime = OutTime / 60          'Convert from seconds to minutes
temp = Round(OutTime, pDP)      'Round to specified decimal places
If temp < MaxMin Then           'If < max mins, do it in minutes
  FmtTime = FormatNumber(OutTime, pDP) & " min"
  GoTo Done
End If

'Will it be hours?
OutTime = OutTime / 60          'Convert from minutes to hours
temp = Round(OutTime, pDP)      'Round to specified decimal places
If temp < MaxHrs Then           'If < max hours, do it in hours
  FmtTime = FormatNumber(OutTime, pDP) & " hrs"
  GoTo Done
End If

'Will it be days?
OutTime = OutTime / 24          'Convert from hours to days
temp = Round(OutTime, pDP)      'Round to specified decimal places
If temp < MaxDys Then           'If < max days, do it in days
  FmtTime = FormatNumber(OutTime, pDP) & " dys"
  GoTo Done
End If

'Will it be weeks?
OutTime = OutTime / 7           'Convert from days to weeks
temp = Round(OutTime, pDP)      'Round to specified decimal places
If temp < MaxWks Then           'If < max weeks, do it in weeks
  FmtTime = FormatNumber(OutTime, pDP) & " wks"
  GoTo Done
End If

'It has to be years
OutTime = OutTime / 365         'Convert it to years
FmtTime = FormatNumber(OutTime, pDP) & " yrs"

Done:
If pTime < 0 Then FmtTime = "-" & FmtTime 'If it was negative, add negative sign

End Function
 
Upvote 0
ok - not exactly the prettiest solution, but tell me what you think about this:
cell E2:
Code: 
=IF(B2="sec",A2,IF(B2="min",A2*60,IF(B2="hour",A2*60*60,IF(B2="day",A2*60*60*24,IF(B2="week",A2*60*60*24*7,IF(B2="month",A2*60*60*24*7*30,IF(B2="year",A2*60*60*24*7*30*12,0)))))))

cell F2:
Code: 
=IF(ROUND(E2,C2)<60,"sec",IF(ROUND(E2,C2)<3600,"min",IF(ROUND(E2,C2)<86400,"hour",IF(ROUND(E2,C2)<604800,"day",IF(ROUND(E2,C2)<2592000,"week",IF(ROUND(E2,C2)<31104000,"month","year"))))))

cell G2:
Code: 
=ROUND(IF(F2="sec",E2,IF(F2="min",E2/60,IF(F2="hour",E2/3600,IF(F2="day",E2/86400,IF(F2="week",E2/604800,IF(F2="month",E2/2592000,E2/31104000)))))),C2)&" "&F2

then hide columns E & F?
 
Upvote 0
mikeross said:
ok - not exactly the prettiest solution, but tell me what you think about this:
Click to expand...

Thanks, Mike, but I really want a UDF solution. Even if the cell formula works, I have to remember it and copy it without making a mistake. It's just too messy. :eeek:
 
Upvote 0
You must log in or register to reply here.

Similar threads

PS_Richard
  • Solved
Calculate Time Difference Per Day Based On Criteria
Replies
5
Views
766
Anthony47
A
S
  • Question Question
Calculating Time Units - VBA User Form
Replies
0
Views
2K
shebe228
S
M
  • Question Question
List of time data using start and end times and presenting duration as an hourly table
Replies
1
Views
2K
RasGhul
RasGhul
JenniferMurphy
  • Question Question
Variable formatting of time values
2
Replies
12
Views
2K
JenniferMurphy
JenniferMurphy
M
  • Question Question
problems displaying time formats
Replies
9
Views
850
Richard U
Richard U

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top