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?

 

[JenniferMurphy]

I have what I think is a working version of this FmtTime UDF. I have uploaded a xlsm workbook with the code in a code module to DropBox. I think it's fairly well documented and he workbook included a sheet that tests quite a few parameter combinations.

Here's the syntax:

   Syntax: =FmtTime(pTime, [pDP|1], [pInUnits|"Secs"], [pNegOK|False]

          pTime     The numeric time value to be formatted
          pDP       Decimal places in result
          pInUnits  The pTime units: Seconds, Minutes, Hours, Days, Weeks, Years
                                     Various abbreviations (see below)
          pNegOK    False/0 = suppress negative values
                    True/x  = Process them

Here's the link to the DropBox folder.

https://www.dropbox.com/s/9yrkr1uct1u240n/Mr Excel FmtTime.xlsm?dl=0

I would appreciate any comments or suggestions.

 

[JenniferMurphy]

Ooops. I forgot to include the RangeName UDF. I just uploaded a corrected version.

 

[JenniferMurphy]

OMG! I completely screwed up. I didn't understand how to move code from an add-in to a code module in a workbook. The workbook I uploaded had the complete path to the add-in module in every call to the UDFs. I am in the process of fixing and testing this. In the meantime, I have removed the workbook from Dropbox. I will post again when I have a properly working version.

My apologies. I'm just learning how to do this.