Formatting Elapsed Time: FormatInterval()
2007-09-09
Recently, for an availability chart I was creating, I needed to take an elapsed time (a server outage) and format that time into the form of "n days n hours n minutes n seconds" of time. Excel doesn't offer that functionality as a native function, and I was also unable to find anything on the net; so, I wrote something. This function is the result.
To use this function, download the following file: FormatInterval_v3.zip
You can copy the source code out of the file or "FileàSave As" to install it as an Add-In. Installation instructions are enclosed in the file.
FormatInterval(<Interval In Seconds>, [<Use Short Names?>], <Format String>)
Format String values: y = Year M = Month w = Week d = Day h = Hour m = Minute s = Second
FormatInterval() performs its interval calculation assuming a year is 365.25 days in length. This means that it sometimes returns a different value than you expect it to return.
FormatInterval() is passed a time interval in seconds, and the function emits a string that describes the elapsed time in terms of the number of months, weeks, days, hours, minutes, and seconds of elapsed time. Wherever one of those time components is zero, it is not shown. If the second parameter is specified and is non-zero then abbreviations will be used (e.g., "w" for "week"). Negative interval values will return a result of "#VALUE!".
Short Names? = null or False() Format String Result s = 12960 seconds yMwdh = 3.6 hours yMwdhs = 3 hours 2160 seconds yMwdhm = 3 hours 36 minutes yMwdhms = 3 hours 36 minutes s = 47329920 seconds yMwdh = 2 years 11 months 4 weeks 2 days 6.9 hours yMwdhs = 2 years 11 months 4 weeks 2 days 6 hours 3240 seconds yMwdhm = 2 years 11 months 4 weeks 2 days 6 hours 54 minutes yMwdhms = 2 years 11 months 4 weeks 2 days 6 hours 54 minutes
Short Names? = True() Format String Result s = 12960s. yMwdh = 3.6h. yMwdhs = 3h. 2160s. yMwdhm = 3h. 36m. yMwdhms = 3h. 36m. s = 12960s. yMwdh = 3.6h. yMwdhs = 3h. 2160s. yMwdhm = 3h. 36m. yMwdhms = 3h. 36m.
Note, the code is Copyright ©2005–2007 Christopher Rath. Permission is granted for any use of this code by others as long as this copyright statement is retained, other due and proper credit is provided to the author (e.g., don't claim you wrote this template), and the LGPL license is respected. This package is free software; you can redistribute it and/or modify it under the terms of version 2.1 of the GNU Lesser General Public License as published by the Free Software Foundation. See the LGPL license on the GNU.org website. This package is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE; on an “AS IS,” “WHERE IS” and “WITH ALL FAULTS” basis.