Variable formatting of time values

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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)

R/CCDEF
3StartEndElapsedFormula
403/13/1903/19/196.0 dysE4: =fmttime(D4-C4,"day")
502/22/1903/19/193.6 wksE5: =fmttime(D5-C5,"day")
611/02/1703/19/191.4 yrsE6: =fmttime(D6-C6,"day")
707/04/0203/19/1916.7 yrsE7: =fmttime(D7-C7,"day")

<tbody>
</tbody>

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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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

Moderator
Joined
Oct 23, 2004
Messages
9,899
=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
 
Last edited:

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
=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

Moderator
Joined
Oct 23, 2004
Messages
9,899
do you have date AND times, because they should be calculable by hours and then proved with an IF
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,220
Office Version
365
Platform
Windows
Hi, here is a UDF you could try:

Code:
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.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">start</td><td style=";">end</td><td style=";">result</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">03/13/2019 00:00:00</td><td style="text-align: right;;">03/19/2019 00:00:00</td><td style="background-color: #FFFF00;;">6.0 days</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">02/22/2019 00:00:00</td><td style="text-align: right;;">03/19/2019 00:00:00</td><td style=";">3.6 weeks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">11/02/2017 00:00:00</td><td style="text-align: right;;">03/19/2019 00:00:00</td><td style=";">1.4 years</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">07/04/2002 00:00:00</td><td style="text-align: right;;">03/19/2019 00:00:00</td><td style=";">16.7 years</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">01/01/2019 00:00:00</td><td style="text-align: right;;">01/31/2019 00:00:00</td><td style=";">4.3 weeks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">01/01/2019 10:00:00</td><td style="text-align: right;;">01/01/2019 12:35:00</td><td style=";">2.6 hours</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">01/01/2019 10:00:00</td><td style="text-align: right;;">01/01/2019 10:35:00</td><td style=";">35.0 mins</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">01/01/2019 10:00:00</td><td style="text-align: right;;">01/01/2019 10:00:55</td><td style=";">55.0 secs</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">01/01/2019 10:00:00</td><td style="text-align: right;;">01/04/2025 10:00:55</td><td style=";">6.0 years</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=fmttime(<font color="Blue">A2,B2</font>)</td></tr></tbody></table></td></tr></table><br />
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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.

R/CCDEFGHI
3Elapsed
4BeginValueUnitsDaysEndFmt_TimeRound
503/21/19 01:15:001.941000000mn0.00134791666703/21/19 01:16:561.9 min1.90
603/21/19 01:15:001.942000000mn0.00134861111103/21/19 01:16:572.0 min1.90
703/21/19 01:15:0017.949800000hr0.74790833333303/21/19 19:11:5917.9 hrs17.90
803/21/19 01:15:0017.949900000hr0.74791250000003/21/19 19:12:0018.0 hrs17.90
903/21/19 01:15:005.949994000day5.94999400000003/27/19 00:02:595.9 dys5.90
1003/21/19 01:15:005.949995000day5.94999500000003/27/19 00:03:006.0 dys5.90
1103/21/19 01:15:0023.949999980yr8747.73749269500003/02/43 18:56:5923.9 yrs23.90
1203/21/19 01:15:0023.949999990yr8747.73749634750003/02/43 18:57:0024.0 yrs23.90

<tbody>
</tbody>

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:

Code:
Syntax: =FmtTime(pTime, [pInUnits|"Days"], [pDP|1]
In my coding "standard", the prefix "p" indicates a parameter.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,183
Office Version
365, 2016, 2007
Platform
Windows
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:

Code:
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.
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top