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)
​

R/C	C	D	E	F
3	Start	End	Elapsed	Formula
4	03/13/19	03/19/19	6.0 dys	E4: =fmttime(D4-C4,"day")
5	02/22/19	03/19/19	3.6 wks	E5: =fmttime(D5-C5,"day")
6	11/02/17	03/19/19	1.4 yrs	E6: =fmttime(D6-C6,"day")
7	07/04/02	03/19/19	16.7 yrs	E7: =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]

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.