How Much Time Has Passed - VBA

Uneori avem nevoie sa stim cat timp a trecut intre doua date, in termeni de zile, luni si ani. Simpla scadere a datei mai mici din data mai mare nu este suficienta: numarul rezultat este doar numarul zilelor. Din nefericire, felul in care ne-am structurat timpul in zile, luni si ani nu face usor calculul in acesti termeni: luna poate avea 28, 29, 30 sau 31 de zile; intre 28 februarie si 31 martie avem o luna si 3 zile, iar odata la patru ani avem o luna si 2 zile.

Functia de mai jos simplifica lucrurile. Ea poate fi folosita ca atare. In Excel, va aparea in lista de functii si se vor putea face referinte catre celule. Variabilele declarate la nivel de modul pot fi folosite separat in alte functii sau rutine.

--------------------------------------------------------------------------------------
Sometimes we need to know how much time has elapsed between two dates, in terms of days, months and years. Simply subtracting the smaller date from the larger date is not enough: the resulting number is just the number of days. Unfortunately, the way we have structured our time in days, months and years does not make the calculation easy in these terms: the month can be 28, 29, 30 or 31 days; between February 28 and March 31 we have a month and 3 days, and every four years we have a month and 2 days.

The function below simplifies things. It may be used as such. In Excel, it will appear in the list of functions and will be able to make references to cells. The variables declared at the module level can be used separately in other functions or routines.



'source:  https://code-for-vb.blogspot.com/2019/11/how-much-time-has-passed.html
'please to mention

Option Explicit

Public y As Integer, m As Byte, d As Byte


Public Function TimeSTR(ByRef DataStart As Date, ByRef DataEnd As Date) As String
Dim yearStart As Integer, yearEnd As Integer
Dim monthStart As Byte, monthEnd As Byte
Dim dayStart As Byte, dayEnd As Byte

Dim dayMonthFinal As Byte
Dim inReverse As Boolean, TemporarData As Date



If DataStart = 0 Or DataEnd = 0 Then
   TimeSTR = "no begining / ending date"
   Exit Function
End If

10
Select Case DataEnd >= DataStart
Case True
     yearStart = Year(DataStart)
     yearEnd = Year(DataEnd)

     monthStart = Month(DataStart)
     monthEnd = Month(DataEnd)

     dayStart = Day(DataStart)
     dayEnd = Day(DataEnd)

Case False
     inReverse = True
     
     TemporarData = DataStart
     DataStart = DataEnd
     DataEnd = TemporarData
     
     GoTo 10
End Select

'finding leap years and odd or even months
Select Case monthEnd
Case 1, 3, 5, 7, 8, 10, 12 '31 days
     dayMonthFinal = 31
        
Case 2 'February - see leap years
     Select Case yearEnd Mod 4
     Case 0
          dayMonthFinal = 29
                     
     Case Else
          dayMonthFinal = 28
     End Select
        
Case 4, 6, 9, 11 '30 days
     dayMonthFinal = 30
End Select


'y=years
'm=months
'd=days
Select Case yearStart = yearEnd
Case True 'year
     Select Case monthStart = monthEnd
     Case True 'month
          y = 0
          m = 0
          d = dayEnd - dayStart
          
     Case False 'month
          Select Case dayEnd >= dayStart
          Case True 'day
               y = 0
               m = monthEnd - monthStart
               d = dayEnd - dayStart
               
          Case False 'day
               y = 0
               m = monthEnd - monthStart - 1
               d = dayEnd + dayMonthFinal - dayStart
          End Select 'day
     End Select 'month
     
Case False 'year
     Select Case monthEnd >= monthStart
     Case True 'month
          Select Case dayEnd >= dayStart
          Case True 'day
               y = yearEnd - yearStart
               m = monthEnd - monthStart
               d = dayEnd - dayStart
               
          Case False 'day
               Select Case monthStart = monthEnd
               Case True 'month
                    y = yearEnd - yearStart - 1
                    m = 11 '(12-1)
                    d = dayEnd + dayMonthFinal - dayStart + 1
                    
               Case False 'month
                    y = yearEnd - yearStart
                    m = monthEnd - monthStart - 1
                    d = dayEnd + dayMonthFinal - dayStart
               End Select 'month
          End Select 'day
     
     Case False 'month
          Select Case dayEnd >= dayStart
          Case True 'day
               y = yearEnd - yearStart - 1
               m = 12 - monthStart + monthEnd
               d = dayEnd - dayStart
               
          Case False 'day
               y = yearEnd - yearStart - 1
               m = 12 - monthStart + monthEnd - 1
               d = dayEnd + dayMonthFinal - dayStart
          End Select 'day
     End Select 'month
End Select 'year

TimeSTR = y & " years, " & m & " months, " & d & " days" & IIf(inReverse, " in reverse", "")
End Function