Code Library

Displaying Very Long Human Readable Time Spans

With 29-million-year time spans, you can handle almost any task (besides evolution, continental drift, and adding the LAA flag to MS Access).

Mike Wolfe

• 8 min read
Displaying Very Long Human Readable Time Spans

In yesterday's article, I posted a function named HumanizedSeconds().

Displaying Human Readable Time Spans
The HumanizedSeconds() and ConvertToSeconds() functions work hand-in-hand to provide a general solution for storage and display of time spans.
No Longer SetMike Wolfe

The function converts a time span in seconds into a more user-friendly English equivalent.  For example, there are 604,800 seconds in a week:

However, because the number of seconds was passed as a Long integer, the function was subject to the limits of that data type.  Thus, the longest time span supported was just over 68 years:

Over on LinkedIn, Joakim Dalby asked about this limitation:

Time Spans Longer Than 68 Years

The 68-year maximum time span is a result of the data type I chose to use for the function argument.

The maximum value of the Long data type is 2,147,483,647.  What if we chose a different data type?  Let's consider some alternatives:

  • Double: max of about 1.8 x 10308 (about 5 googol-cubed years; 5 x 10300)
  • LongLong: max of about 9 quintillion (about 292 billion years)
  • Currency: max of about 922 trillion (about 29 million years)

The Double type does not make any sense, because it is a floating-point type. Floating point numbers are not a good choice for storing discrete numbers. Plus, if you are dealing with time spans at this scale, you should probably be storing your data with different units than seconds.  Just saying.

The LongLong type would be a good option, except that it requires 64-bit VBA.  If you can guarantee that all users have 64-bit VBA, then go for it.

The Currency type seems like the obvious choice.  We get to-the-second precision up to and beyond 29 million years.  And it works natively in 32-bit VBA.  The downside is that it provides the shortest maximum time span of the three options.  

That said, 29-million-year time spans should be more than enough to provide estimates for almost any conceivable task, with a few exceptions, such as:

Converting the Code

The code below has been updated from what I posted yesterday to handle much longer time spans:

To convert the code, I made the following changes:

Before

Private Const SecondsPerYear As Long = SecondsPerDay * 365

Function HumanizedSeconds(Seconds As Long) As String
    Dim Years As Long
    Years = Seconds \ SecondsPerYear
    
    '...{code removed for clarity}...
    
    If Years > 0 Then s = Conc(s, Pluralize("# year[s]", Years))
    
    '...{code removed for clarity}...
End Function

Function ConvertToSeconds(Years As Long, Weeks As Long, _
                          Days As Long, Hours As Long, _
                          Minutes As Long, Seconds As Long) As Long
    '...{code removed for clarity}...
End Function

After

Private Const SecondsPerYear As Currency = SecondsPerDay * 365

Function HumanizedSeconds(Seconds As Currency) As String
    Dim Years As Currency
    Years = Int(Seconds / SecondsPerYear)
    
    '...{code removed for clarity}...
    
    If Years > 0 Then s = Conc(s, Pluralize("# year[s]", Years, , "#,###"))
    
    '...{code removed for clarity}...
End Function

Function ConvertToSeconds(Years As Long, Weeks As Long, _
                          Days As Long, Hours As Long, _
                          Minutes As Long, Seconds As Long) As Currency
    '...{code removed for clarity}...
End Function

Integer Division

Most of the changes amounted to replacing Long data types with Currency, but there was one more subtle change that I wanted to highlight.

I had to change the following line from...

Years = Seconds \ SecondsPerYear

...to...

Years = Int(Seconds / SecondsPerYear)

The reason is that the integer division operator (\; i.e., backslash) does not work with values larger than the maximum long integer amount, even if the values are explicitly converted to Currency types prior to the calculation:

Both ?2147483648 \ 10 and ?CCur(2147483648) \ CCur(10) both result in ot

The (Updated) Code

The original code is available for comparison here.

The HumanizedSeconds() function depends on many other functions I have written about in the past, including:

All of these dependencies are included in the sample code below for your convenience.

I'm also using my Python-inspired doc test comments (the ones that start with '>>>) to provide both usage examples and code verification.

Option Compare Database
Option Explicit


'Constants for HumanizedSeconds and ConvertToSeconds functions:
Private Const SecondsPerMinute As Long = 60
Private Const SecondsPerHour As Long = SecondsPerMinute * 60
Private Const SecondsPerDay As Long = SecondsPerHour * 24
Private Const SecondsPerYear As Currency = SecondsPerDay * 365

'>>> HumanizedSeconds(60)
' 1 minute
'>>> HumanizedSeconds(120)
' 2 minutes
'>>> HumanizedSeconds(60 * 60 * 3)
' 3 hours
'>>> HumanizedSeconds(60 * 60 * 24 * 5)
' 5 days
'>>> HumanizedSeconds(60 * 60 * 24 * 29)
' 29 days
'>>> HumanizedSeconds(60 * 60 * 24 * 365)
' 1 year
'>>> HumanizedSeconds(60 * 60 * 24 * 365 * 2)
' 2 years
'>>> HumanizedSeconds(60& * 60 * 24 * 365 * 4 + 60& * 60 * 24 * 14 + 60 * 60 * 3 + 60 * 7 + 33)
' 4 years, 2 weeks, 3 hours, 7 minutes, 33 seconds
'
'If "days" are evenly divisible by 7, they output as "weeks"
'>>> HumanizedSeconds(60 * 60 * 24 * 28)
' 4 weeks
'>>> HumanizedSeconds(60 * 60 * 24 * 28 + 60 * 60 * 19 + 60 * 3 + 22)
' 4 weeks, 19 hours, 3 minutes, 22 seconds
'
'Supports time spans of over 29 million years
'>>> HumanizedSeconds(922337203685477)
' 29,247,120 years, 316 days, 17 hours, 31 minutes, 17 seconds
'
' ----------------------------------------------------------------
' Procedure : HumanizedSeconds
' Author    : Mike Wolfe <mike@nolongerset.com>
' Date      : 3/9/2022 - 4/14/2022
' Source    : https://nolongerset.com/very-long-time-spans/
' Purpose   : Returns a human friendly description of the timeframe passed in seconds.
' Requires  - StringFunctions.Pluralize() function
' Notes - "Years" are assumed to be exactly 365 days (leap days not considered)
'       - "Months" never appear in output because their length varies (28 - 31 days)
'       - "Weeks" only show if number of days is evenly divisible by 7
'       - "Weeks" and "Days" never appear in the same output
' ----------------------------------------------------------------
Function HumanizedSeconds(Seconds As Currency) As String
    Dim Years As Currency
    Years = Int(Seconds / SecondsPerYear)
    
    Dim Days As Long
    Days = (Seconds - (Years * SecondsPerYear)) \ SecondsPerDay
    
    Dim Hours As Long
    Hours = (Seconds _
           - (Years * SecondsPerYear) _
           - (Days * SecondsPerDay)) \ SecondsPerHour
    
    Dim Minutes As Long
    Minutes = (Seconds _
             - (Years * SecondsPerYear) _
             - (Days * SecondsPerDay) _
             - (Hours * SecondsPerHour)) \ SecondsPerMinute
    
    Dim Secs As Long
    Secs = (Seconds _
          - (Years * SecondsPerYear) _
          - (Days * SecondsPerDay) _
          - (Hours * SecondsPerHour) _
          - (Minutes * SecondsPerMinute))
    
    Dim Weeks As Long
    If Days Mod 7 = 0 Then
        Weeks = Days / 7
        Days = 0
    End If
    
    Dim s As String
    If Years > 0 Then s = Conc(s, Pluralize("# year[s]", Years, , "#,###"))
    If Weeks > 0 Then s = Conc(s, Pluralize("# week[s]", Weeks))
    If Days > 0 Then s = Conc(s, Pluralize("# day[s]", Days))
    If Hours > 0 Then s = Conc(s, Pluralize("# hour[s]", Hours))
    If Minutes > 0 Then s = Conc(s, Pluralize("# minute[s]", Minutes))
    If Secs > 0 Then s = Conc(s, Pluralize("# second[s]", Secs))
    
    HumanizedSeconds = s
End Function

'>>> ConvertToSeconds( 0,  0,  0,  0,  0,  1)
' 1
'>>> ConvertToSeconds( 0,  0,  0,  0,  1,  0)
' 60
'>>> ConvertToSeconds( 0,  0,  0,  1,  0,  0)
' 3600
'>>> ConvertToSeconds( 0,  0,  1,  0,  0,  0)
' 86400
'>>> ConvertToSeconds( 0,  1,  0,  0,  0,  0)
' 604800
'>>> ConvertToSeconds( 1,  0,  0,  0,  0,  0)
' 31536000
'>>> ConvertToSeconds(10,  0,  0,  0,  0,  0)
' 315360000
'
' 2147483647 is the maximum value for the Long data type:
'>>> ConvertToSeconds(68,  5,  0,  3,  14,  7)
' 2147483647
'
' 922337203685477 is the maximum value of the Currency data type:
'>>> ConvertToSeconds(29247120,0,316,17,31,17)
' 922337203685477
' ----------------------------------------------------------------
' Procedure : ConvertToSeconds
' Author    : Mike Wolfe <mike@nolongerset.com>
' Date      : 3/9/2022 - 4/14/2022
' Source    : https://nolongerset.com/very-long-time-spans/
' Purpose   : Converts from a variety of time units into seconds.
' Notes     - This is a companion function to the HumanizedSeconds() function.
' ----------------------------------------------------------------
Function ConvertToSeconds(Years As Long, Weeks As Long, _
                          Days As Long, Hours As Long, _
                          Minutes As Long, Seconds As Long) As Currency
    Const SecondsPerWeek As Long = SecondsPerDay * 7
    
    ConvertToSeconds = Years * SecondsPerYear _
                     + Weeks * SecondsPerWeek _
                     + Days * SecondsPerDay _
                     + Hours * SecondsPerHour _
                     + Minutes * SecondsPerMinute _
                     + Seconds
End Function



'----------------------------'
'                            '
'   Additional dependencies  '
'                            '
'----------------------------'


'---------------------------------------------------------------------------------------
' Procedure : Pluralize
' Author    : Mike Wolfe <mike@nolongerset.com>
' Source    : https://nolongerset.com/the-pluralize-function/
' Purpose   : Formats a phrase to make verbs agree in number.
' Notes     : To substitute the absolute value of the number for numbers that can be
'               positive or negative, use a custom number format that includes
'               both positive and negative formats; e.g., "#;#".
'---------------------------------------------------------------------------------------
'
Function Pluralize(Text As String, Num As Variant, _
                   Optional NumToken As String = "#", _
                   Optional NumFormat As String = "")
    
    Const OpeningBracket As String = "\["
    Const ClosingBracket As String = "\]"
    Const OpeningBrace As String = "\{"
    Const ClosingBrace As String = "\}"
    Const DividingSlash As String = "/"
    Const CharGroup As String = "([^\]]*)"  'Group of 0 or more characters not equal to closing bracket
    Const BraceGroup As String = "([^\/\}]*)" 'Group of 0 or more characters not equal to closing brace or dividing slash

    Dim IsPlural As Boolean, IsNegative As Boolean
    If IsNumeric(Num) Then
        IsPlural = (Abs(Num) <> 1)
        IsNegative = (Num < 0)
    End If
    
    Dim Msg As String, Pattern As String
    Msg = Text
    
    'Replace the number token with the actual number
    Msg = Replace(Msg, NumToken, Format(Num, NumFormat))
    
    'Replace [y/ies] style references
    Pattern = OpeningBracket & CharGroup & DividingSlash & CharGroup & ClosingBracket
    Msg = RegExReplace(Pattern, Msg, "$" & IIf(IsPlural, 2, 1))
    
    'Replace [s] style references
    Pattern = OpeningBracket & CharGroup & ClosingBracket
    Msg = RegExReplace(Pattern, Msg, IIf(IsPlural, "$1", ""))
        
    'Replace {gain/loss} style references
    Pattern = OpeningBrace & BraceGroup & DividingSlash & BraceGroup & ClosingBrace
    Msg = RegExReplace(Pattern, Msg, "$" & IIf(IsNegative, 2, 1))
        
    Pluralize = Msg
    
End Function


'---------------------------------------------------------------------------------------
' Procedure : RegExReplace
' Author    : Mike Wolfe <mike@nolongerset.com>
' Source    : https://nolongerset.com/now-you-have-two-problems/
' Purpose   : Attempts to replace text in the TextToSearch with text and back references
'               from the ReplacePattern for any matches found using SearchPattern.
' Notes     - If no matches are found, TextToSearch is returned unaltered.  To get
'               specific info from a string, use RegExExtract instead.
'---------------------------------------------------------------------------------------
'
Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = MultiLine
        .Global = GlobalReplace
        .IgnoreCase = IgnoreCase
        .Pattern = SearchPattern
    End With
    
    RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function

'---------------------------------------------------------------------------------------
' Procedure : Conc
' Author    : Mike Wolfe <mike@nolongerset.com>
' Source    : https://nolongerset.com/come-together/
' Purpose   : Concatenates two strings
' Notes     : Eliminates the need to strip off the leading/trailing delimiter when
'               building a string list
'---------------------------------------------------------------------------------------
'
Function Conc(StartText As Variant, NextVal As Variant, _
              Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function

Image by Stefan Keller from Pixabay

UPDATE [2022-04-15]: Updated ConvertToSeconds() function to return Currency instead of Long (h/t Joakim Dalby).

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)


All original code samples by Mike Wolfe are licensed under CC BY 4.0