Variable formatting of time values

[JenniferMurphy]

Is there a function, add-in, or easy way to format time values so that the units displayed vary with the magnitude of the value?

What I mean is that the function will convert the value to the units that will be >1 but less that "1" of the next higher units.

• If the value is less than one minute (60 seconds), it will be formatted as seconds.
• If it is less than 1 hour (60 minutes), it will be formatted as minutes.
• If it less less than 1 day (24 hours), it will be formatted as hours.
• If it is less than 1 week (7 days), it will be formatted as days.
• If it is less than 1 month (12.175 days), it will be formatted as weeks.
• If it is less that 1 year (325.25 days), it will be formatted as months.
• Otherwise, it will be formatted as years.

I have run into situations where I need to calculate how long it has been between two dates. The elapsed time can be anywhere from a few days to 20 years. Displaying "7.5 years" as "2,739.374 days" or "25 days" as "0.068 years" is not helpful.

I am looking for something like this where a mythical FmtTime function has this syntax:

=FmtTime(InValue, InUnits)
​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]Elapsed[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]03/13/19[/TD]
[TD="align: center"]03/19/19[/TD]
[TD="align: right"]6.0 dys[/TD]
[TD="align: center"]E4: =fmttime(D4-C4,"day")[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]02/22/19[/TD]
[TD="align: center"]03/19/19[/TD]
[TD="align: right"]3.6 wks[/TD]
[TD="align: center"]E5: =fmttime(D5-C5,"day")[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]11/02/17[/TD]
[TD="align: center"]03/19/19[/TD]
[TD="align: right"]1.4 yrs[/TD]
[TD="align: center"]E6: =fmttime(D6-C6,"day")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]07/04/02[/TD]
[TD="align: center"]03/19/19[/TD]
[TD="align: right"]16.7 yrs[/TD]
[TD="align: center"]E7: =fmttime(D7-C7,"day")[/TD]
[/TR]
</tbody>[/TABLE]

I have a crude function that handles years, months, and days, but it has some glitches. I'd like something more general and robust.

Any pointers?

 

[mole999]

if statement with a text format ss, m, [h], d, etc

 

[JenniferMurphy]

if statement with a text format ss, m, [h], d, etc

It seems like that would one mind-numbingly complicated If statement -- at least for my feeble brain.

Could you post an partial example that I could go from?

 

[mole999]

=IF(C1< TIME(0,1,0),TEXT(C1,"s")&" Sec") for a starter, but what lets the formula know which group by, unless everything is calculated from the second value

 

[JenniferMurphy]

=IF(C1< TIME(0,1,0),TEXT(C1,"s")&" Sec") for a starter,

This IF statement tests just the start date. I am interested in the interval between the start and end dates. So the IF statement would have to be something like "=IF((D4-C4)<...".

This might work for hours, minutes, and seconds using nested IF statements. But how would I extend it for days, weeks, months, and years?
<time(0,1,0)...".

but what lets the formula know which group by, unless everything is calculated from the second value

I don't understand this. Is this a question?
</time(0,1,0)...".

 

[mole999]

do you have date AND times, because they should be calculable by hours and then proved with an IF

 

[FormR]

Hi, here is a UDF you could try:

Function fmttime(dte_str, dte_end)
Dim seconds_diff As Long
seconds_diff = Abs((dte_end - dte_str) * 86400) 'time difference in seconds
Select Case seconds_diff
    Case Is < 60:       fmttime = Format(seconds_diff, "0.0 secs")
    Case Is < 3600:     fmttime = Format(seconds_diff / 60, "0.0 mins")
    Case Is < 86400:    fmttime = Format(seconds_diff / 3600, "0.0 hours")
    Case Is < 604800:   fmttime = Format(seconds_diff / 86400, "0.0 days")
    Case Is < 2626560:  fmttime = Format(seconds_diff / 604800, "0.0 weeks")
    Case Is < 31557600: fmttime = Format(seconds_diff / 2626560, "0.0 months")
    Case Else:          fmttime = Format(seconds_diff / 31557600, "0.0 years")
End Select
End Function

• if it is less than 1 month (12.175 days), it will be formatted as weeks.
• if it is less that 1 year (325.25 days), it will be formatted as months.

Note, I used 30.4 days for 1 month and 365.25 days for a year - hopefully, you can see what to change if you wanted to use something different.

Excel 2013/2016
	A	B	C
1	start	end	result
2	03/13/2019 00:00:00	03/19/2019 00:00:00	6.0 days
3	02/22/2019 00:00:00	03/19/2019 00:00:00	3.6 weeks
4	11/02/2017 00:00:00	03/19/2019 00:00:00	1.4 years
5	07/04/2002 00:00:00	03/19/2019 00:00:00	16.7 years
6	01/01/2019 00:00:00	01/31/2019 00:00:00	4.3 weeks
7	01/01/2019 10:00:00	01/01/2019 12:35:00	2.6 hours
8	01/01/2019 10:00:00	01/01/2019 10:35:00	35.0 mins
9	01/01/2019 10:00:00	01/01/2019 10:00:55	55.0 secs
10	01/01/2019 10:00:00	01/04/2025 10:00:55	6.0 years
Sheet1

Cell Formulas
Range	Formula
C2	=fmttime(A2,B2)

 

[James006]

Hello,

Since you have expressed weeks, months and years with the common ' Day ' unit ...

you are left with 60 minutes ( = 1/24 ) and 60 seconds ( =1/1440 )

Whenever you are calculating your D4-C4 difference, the result will fall into one of your 7 categories ... which will determine your Time Unit ...

Better then a UDF ... it seems to me an event macro could easily achieve your objective ...

Hope this will help

 

[JenniferMurphy]

Hi, here is a UDF you could try:

Thanks for that. I did a little testing and it seems to work quite well. I did find one little glitch involving rounding.

• Col C has the start date.
• Col D has an elapsed time in the units in Col E.
• Col F uses the Convert function to convert that to units of days.
• Col G uses the value in Col F to calculate the end date.
• Col H calls your function. I had to rename it slightly because I had already started work on my own version by the same name.
• Col I shows the value in Col F rounded to 1 decimal place using the Round function.

In rows 6, 8, 10, & 12, Fmt_Time incorrectly round up.

[TABLE="class: grid, width: 780"]
<tbody>[TR]
[TD="align: center"]R/C[/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]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="colspan: 3, align: center"]Elapsed[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Begin[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]Units[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]Fmt_Time[/TD]
[TD="align: center"]Round[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]1.941000000[/TD]
[TD="align: center"]mn[/TD]
[TD="align: right"]0.001347916667[/TD]
[TD="align: right"]03/21/19 01:16:56[/TD]
[TD="align: right"]1.9 min[/TD]
[TD="align: right"]1.90[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]1.942000000[/TD]
[TD="align: center"]mn[/TD]
[TD="align: right"]0.001348611111[/TD]
[TD="align: right"]03/21/19 01:16:57[/TD]
[TD="align: right"]2.0 min[/TD]
[TD="align: right"]1.90[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]17.949800000[/TD]
[TD="align: center"]hr[/TD]
[TD="align: right"]0.747908333333[/TD]
[TD="align: right"]03/21/19 19:11:59[/TD]
[TD="align: right"]17.9 hrs[/TD]
[TD="align: right"]17.90[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]17.949900000[/TD]
[TD="align: center"]hr[/TD]
[TD="align: right"]0.747912500000[/TD]
[TD="align: right"]03/21/19 19:12:00[/TD]
[TD="align: right"]18.0 hrs[/TD]
[TD="align: right"]17.90[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]5.949994000[/TD]
[TD="align: center"]day[/TD]
[TD="align: right"]5.949994000000[/TD]
[TD="align: right"]03/27/19 00:02:59[/TD]
[TD="align: right"]5.9 dys[/TD]
[TD="align: right"]5.90[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]5.949995000[/TD]
[TD="align: center"]day[/TD]
[TD="align: right"]5.949995000000[/TD]
[TD="align: right"]03/27/19 00:03:00[/TD]
[TD="align: right"]6.0 dys[/TD]
[TD="align: right"]5.90[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]23.949999980[/TD]
[TD="align: center"]yr[/TD]
[TD="align: right"]8747.737492695000[/TD]
[TD="align: right"]03/02/43 18:56:59[/TD]
[TD="align: right"]23.9 yrs[/TD]
[TD="align: right"]23.90[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]03/21/19 01:15:00[/TD]
[TD="align: right"]23.949999990[/TD]
[TD="align: center"]yr[/TD]
[TD="align: right"]8747.737496347500[/TD]
[TD="align: right"]03/02/43 18:57:00[/TD]
[TD="align: right"]24.0 yrs[/TD]
[TD="align: right"]23.90[/TD]
[/TR]
</tbody>[/TABLE]

I am working on a solution that involves rounding before testing, but I would be interested in any ideas or suggestions you might have.

In my general solution, I will have parameters for decimal places and units so the caller can pass the time value in any units and display the result rounded to any number of decimal places. It might look something like this:

Syntax: =FmtTime(pTime, [pInUnits|"Days"], [pDP|1]

In my coding "standard", the prefix "p" indicates a parameter.

 

[JenniferMurphy]

In my general solution, I will have parameters for decimal places and units so the caller can pass the time value in any units and display the result rounded to any number of decimal places. It might look something like this:

Syntax: =FmtTime(pTime, [pInUnits|"Days"], [pDP|1]

In my coding "standard", the prefix "p" indicates a parameter.

PS: In my solution, the UDF will be passed the time interval instead of the start and end time stamps. For my needs, that is a more general syntax. I know I said originally, that I needed this to format elapsed times, but sometimes it will be used with timings, as with a stopwatch. That's where hours, minutes, and seconds come into play.