excelvbadate
Calculate exact difference between dates
I need to calculate days and months between dates, ensuring accuracy when crossing month boundaries.
I assume Wolfram Alpha results are correct so I want the same results from my VBA code.
For example:
The difference between "31/03/1955" and "26/05/2024" should be:
- 69 anni (years), 1 mese (month), 26 giorni (days)
- 25,259 giorni totali (total days)
The difference between "30/03/1955" and "26/05/2024" should be:
- 69 anni (years), 1 mese (month), 27 giorni (days)
- 25,260 giorni totali (total days)
My code in this last case, starting date "30/03/1955" calculates:
- 69 anni (years), 1 mese (month), 26 giorni (days) - incorrect, same as starting date "31/03/1955"
- 25,260 giorni totali (total days) - correct
Option Explicit
Public Function DiffDate44(ByVal data1 As Variant, ByVal data2 As Variant, dato_richiesto As String, valori_assoluti As Boolean) As Variant
Dim anni As Integer, mesi As Integer, giorni As Integer
Dim giorni_totali As Long
Dim dummy As Date, tempDate As Date
Dim anniStr As String, mesiStr As String, giorniStr As String, giorni_totaliStr As String, spaziatura As String
On Error GoTo ErrorHandler
' Convert inputs to Date type
data1 = CDate(data1)
data2 = CDate(data2)
' Handle absolute values if required
If valori_assoluti = True Then
If data1 > data2 Then
dummy = data1
data1 = data2
data2 = dummy
End If
Else
If data1 > data2 Then
MsgBox "ERRORE: Data1 > Data2!"
DiffDate = "ERRORE DiffDate"
Exit Function
End If
End If
' Calculate total days difference
giorni_totali = Abs(DateDiff("d", data1, data2))
' Calculate years
anni = DateDiff("yyyy", data1, data2)
If DateSerial(Year(data2), Month(data1), Day(data1)) > data2 Then
anni = anni - 1
End If
' Calculate months
tempDate = DateAdd("yyyy", anni, data1)
mesi = DateDiff("m", tempDate, data2)
If DateAdd("m", mesi, tempDate) > data2 Then
mesi = mesi - 1
End If
' Calculate days
giorni = DateDiff("d", DateAdd("m", mesi, DateAdd("yyyy", anni, data1)), data2)
If giorni < 0 Then
mesi = mesi - 1
tempDate = DateAdd("m", mesi, DateAdd("yyyy", anni, data1))
giorni = DateDiff("d", tempDate, data2)
End If
' Adjust for cases where the months calculation might be incorrect
If mesi < 0 Then
mesi = mesi + 12
anni = anni - 1
End If
' Construct output strings
If anni <> 1 Then
anniStr = " anni"
Else
anniStr = " anno"
End If
If mesi <> 1 Then
mesiStr = " mesi"
Else
mesiStr = " mese"
End If
If giorni <> 1 Then
giorniStr = " giorni"
Else
giorniStr = " giorno"
End If
If anni = 0 And mesi = 0 And giorni = giorni_totali Then
giorni_totaliStr = ""
Else
giorni_totaliStr = " (" & CStr(Format(giorni_totali, "#,###")) & " giorni totali)"
End If
' Return requested output
Select Case dato_richiesto
Case "anni"
DiffDate = anni
Case "mesi"
DiffDate = mesi
Case "giorni"
DiffDate = giorni
Case "giorni_totali"
DiffDate = giorni_totali
Case "stringa1"
DiffDate = mesi & mesiStr & ", " & giorni & giorniStr
Case "stringa2"
DiffDate = mesi & mesiStr & ", " & giorni & giorniStr & giorni_totaliStr
Case "nascondi_valori_a_zero"
If anni > 1 Then
anniStr = CStr(anni) & " anni"
If mesi > 1 Or giorni > 1 Then
spaziatura = ", "
End If
ElseIf anni = 1 Then
anniStr = CStr(anni) & " anno"
If mesi >= 1 Or giorni >= 1 Then
spaziatura = ", "
End If
Else
anniStr = ""
spaziatura = ""
End If
anniStr = anniStr & spaziatura
If mesi > 1 Then
mesiStr = CStr(mesi) & " mesi"
If giorni >= 1 Then
spaziatura = ", "
End If
ElseIf mesi = 1 Then
mesiStr = CStr(mesi) & " mese"
If giorni >= 1 Then
spaziatura = ", "
End If
Else
mesiStr = ""
spaziatura = ""
End If
mesiStr = mesiStr & spaziatura
If giorni > 1 Then
giorniStr = CStr(giorni) & " giorni"
ElseIf giorni = 1 Then
giorniStr = CStr(giorni) & " giorno"
Else
giorniStr = ""
End If
DiffDate = TrueTrim(CStr(anniStr & mesiStr & giorniStr & giorni_totaliStr))
If DiffDate = "" Then DiffDate = "nessuna"
Case "stringa3"
DiffDate = anni & anniStr & ", " & mesi & mesiStr & ", " & giorni & giorniStr
Case "stringa4"
DiffDate = anni & anniStr & ", " & mesi & mesiStr & ", " & giorni & giorniStr & giorni_totaliStr
Case "prossimo_compleanno"
DiffDate = data2 & " (" & Giorno_Settimana(data2) & ")"
End Select
Exit Function
ErrorHandler:
MsgBox "Invalid date format. Please enter valid dates."
DiffDate = "ERRORE Invalid Date"
End Function
Solution
Adding my own code because other answers are incorrect and want you to believe that duplicates and gaps in results are perfectly acceptable.
Updated: added CountDateOnLastDayAsFullMonth boolean flag to treat end dates that fall on the last day of the month to count as full months (this will create dup results for some date ranges)
Public Function DateDiffToText(ByVal StartDate As Date, ByVal EndDate As Date, Optional ByVal CountDateOnLastDayAsFullMonth As Boolean = False)
' expresses a date range difference in years, months and days
' useful for countdown timers or age calcs
'
' Set CountDateOnLastDayAsFullMonth = true if you want end dates that fall on the last day of the month to count as full months
' - this will create dup results for some date ranges
'
' year calc - counts any complete calendar years between dates + one year if start day/month is < end day/month
' Month calc - similiar to year calc - counts complete months (regardless of days) between dates
' on the remainder after subtracting complete years + one month if start day of month (DOM) < end DOM
'
' Day calc - based on the DOM - if start DOM <= end DOM then just the days remaining from start to end DOM,
' else we calc days remaining in start month and add days in end month
'
' By: @DJ. - David Johnston - Burnaby, Canada
Dim Years As Integer
Dim Months As Integer
Dim Days As Integer
Dim StartYear As Integer
Dim StartMonth As Integer
Dim StartDay As Integer
Dim StartMonthDay As Integer
Dim EndYear As Integer
Dim EndMonth As Integer
Dim EndDay As Integer
Dim EndMonthDay As Integer
Dim TempMonth As Integer
Dim TempDate As Date
Dim TempDate2 As Date
' split up date parts
StartYear = Year(StartDate)
StartMonth = Month(StartDate)
StartDay = Day(StartDate)
StartMonthDay = (StartMonth * 100) + StartDay
EndYear = Year(EndDate)
EndMonth = Month(EndDate)
EndDay = Day(EndDate)
EndMonthDay = (EndMonth * 100) + EndDay
'Calculate Years
Years = EndYear - StartYear - 1
If EndMonthDay >= StartMonthDay Then Years = Years + 1
'Calculate Months
TempMonth = EndMonth
If EndMonthDay < StartMonthDay Then TempMonth = TempMonth + 12
Months = TempMonth - StartMonth - 1
If EndDay >= StartDay Then Months = Months + 1
' Calculate Days
If StartDay <= EndDay Then
Days = EndDay - StartDay
Else
'get number of days remaing in month by getting last date of the month
TempDate = DateAdd("m", 1, StartDate)
TempDate = DateSerial(Year(TempDate), Month(TempDate), 1)
TempDate = DateAdd("d", -1, TempDate)
If CountDateOnLastDayAsFullMonth = True Then
TempDate2 = DateAdd("m", 1, EndDate)
TempDate2 = DateSerial(Year(TempDate2), Month(TempDate2), 1)
TempDate2 = DateAdd("d", -1, TempDate2)
If EndDate = TempDate2 Then
'special case - end date is last day of month - count it as a full month
Days = Day(TempDate) - StartDay
Months = Months + 1
If Months = 12 Then
Months = 0
Years = Years + 1
End If
Else
Days = Day(TempDate) - StartDay + EndDay
End If
Else
Days = Day(TempDate) - StartDay + EndDay
End If
End If
DateDiffToText = CStr(Years) & " year(s), " & CStr(Months) & " month(s), " & CStr(Days) & " day(s)"
End Function
Results:
Updated: Results with CountDateOnLastDayAsFullMonth = True
- Constrain a Specman list so it doesn't have identical values in consecutive elements
- How to type cast a list of uint to a list of vr_ahb_data in Specman?
- Static fields/methods in e
- What is the difference between deep_copy and gen keeping in Specman?
- Specman UVM: What is the difference between write_reg { .field == 2;}; and write_reg_fields?
- Does Specman support optional parameters to a method?
- Specman e: When colon equal sign ":=" should be used?
- Specman e: Is there a way to know how many values there is in an enumerated type?
- Specman e error: No match for file when using "for each line in file"
- How to run e file one by one? Not in parallel test
- Specman/e constraint (for each in) iteration
- Specman e: How driver's items queue can be locked from a sequence?
- Specman e: How to print variable's address?
- Specman e: "keep type .. is a" fails to refine the type of a field
- Specman soft select on variable, decimal vs. hexadecimal values
- Specman e: How to print a pointer to a struct?
- Specman e: Is there a way to print unit instance name?
- Specman e: simultaneous events error
- Specman e coverage: ignored values appear in the coverage statistics
- Specman e: How a sequence should be started when gen_and_start_main constrained to FALSE?
- Specman/e list of lists (multidimensional array)
- Does Specman e have struct constructor?
- Specman e: How the predefined sequence.item should be used?
- Specman e: define-as-computed macro error
- Specman e UVM: Why to inherit from uvm_* units?
- Specman e: A sequence drives its BFM also its MAIN was not defined in a test
- e HVL (IEEE 1647): expect expression fails unexpectedly
- Specman e subtyping: How to refer to FALSE value of conditional field in when/extend subtyping?
- e HVL (IEEE 1647): How to set 'X' value?
- Difference between declaring an event that is sensitive to a simple_port value and event_port