Formatting Elapsed Time: FormatInterval()

Christopher Rath

2007-09-09

FormatInterval()

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.

Source Code & Add-In

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.

Syntax

FormatInterval(<Interval In Seconds>, [<Use Short Names?>], <Format String>)

Parameters

Interval In Seconds

The time period in seconds that is to be decomposed into Years, Months, etc. A negative value will return the error string.

Use Short Names?

True() or False(); where True() tells FormatInterval() to use short strings (e.g., s. for seconds) to indicate time periods. Default value is False(); which is actually the value assigned when the parameter is missing.

Format String

A string that tells FormatInterval() how to format its output. Default value is "yMwdhms". See below for further details. Unknown format characters are ignored.

Format String values:
y	=	Year
M	=	Month
w	=	Week
d	=	Day
h	=	Hour
m	=	Minute
s	=	Second

Caveat

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.

Usage

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!".

Examples

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.

Copyright & Licensing

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.