JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,525
- Office Version
- 365
- 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.
I am looking for something like this where a mythical FmtTime function has this syntax:
<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?
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 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?