Calculating Years, Months, Days between two dates

[Mike1234]

Hi can anyone please help!!

I am using the below code to calculate the Years,Months,Days between two dates, but I want to use the current date if my End Date is empty..
How would I do this?


Public Function Calcdays(StartDate As Date, EndDate As Date) As String


Dim intYears As Integer, intMonths As Integer, intDays As Integer
intMonths = DateDiff("m", StartDate, EndDate)
intDays = DateDiff("d", DateAdd("m", intMonths, StartDate), EndDate)
If intDays < 0 Then
intMonths = intMonths - 1
intDays = DateDiff("d", DateAdd("m", intMonths, StartDate), EndDate)
End If
intYears = intMonths \ 12
intMonths = intMonths Mod 12
Calcdays = " " & intYears & " Years, " & intMonths & " Months, " & intDays & " Days"

End Function

My result is =Calcdays([StartDate],[EndDate])

 

[plog]

Public Function Calcdays(StartDate As Date, Optional EndDate As Date = Date()) As String

 

[isladogs]

You may also be interested in this sample database supplied by Pat Hartman earlier today.
It contains just about every date function you are ever likely to need

https://www.access-programmers.co.uk/forums/showpost.php?p=1555496&postcount=10

 

[Mike1234]

Public Function Calcdays(StartDate As Date, Optional EndDate As Date = Date()) As String

I have tried this, but the() after =Date()) keeps disappearing?
I dont know why

 

[The_Doc_Man]

VBA's autocorrect is kicking in, that's why. Question is, does it still work? If so, then VBA's "language-sensitive environment" editor is just showing off.

 

[isladogs]

The brackets shouldn't be there - a very rare event, but on this occasion, plog wrote it incorrectly. It should be:

Public Function Calcdays(StartDate As Date, Optional EndDate As Date) As String

 

[arnelgp]

Function Calcdays(StartDate As Date, Optional EndDate As Variant=0) As String
If IsNull(EndDate) Then EndDate=Date
If EndDate=0Then EndDate=Date

 

[Mike1234]

Function Calcdays(StartDate As Date, Optional EndDate As Variant=0) As String
If IsNull(EndDate) Then EndDate=Date
If EndDate=0Then EndDate=Date

This worked exactly as needed
Thanks
Keep up the good work ALL

 

[Habib Zeb]

Can anyone Help me?

I have used this function to calculate between two dates but when the the date is empty it shows #Type!
anyone please help me if the date is empty then it will also empty.


[/CODE]Option Compare Database

Function DateDiffExact(startDate As Date, endDate As Date) As String
Dim years As Long, months As Long, days As Long
Dim startY As Long, startM As Long, startD As Long
Dim endY As Long, endM As Long, endD As Long
startY = Year(startDate)
startM = Month(startDate)
startD = Day(startDate)
endY = Year(endDate)
endM = Month(endDate)
endD = Day(endDate)
years = endY - startY
months = endM - startM
days = endD - startD
If days < 0 Then
days = days + Day(DateSerial(endY, endM + 1, 0))
months = months - 1
End If
If months < 0 Then
months = months + 12
years = years - 1
End If
DateDiffExact = years & " y | " & months & " m | " & days & " d "

End Function
[/CODE]

 

[theDBguy]

Hi @Habib Zeb

Welcome to AWF!

This is a six-year old thread. You might consider starting a new one instead.

 

[arnelgp]

declare your parameters as Variant:

Function DateDiffExact(ByVal startDate As Variant, ByVal endDate As Variant) As String
    Dim years As Long, months As Long, days As Long
    Dim startY As Long, startM As Long, startD As Long
    Dim endY As Long, endM As Long, endD As Long
    
    'arnelgp
    startDate = Nz(startDate, Date)
    endDate = Nz(endDate, Date)
    
    startY = Year(startDate)
    startM = Month(startDate)
    startD = Day(startDate)
    endY = Year(endDate)
    endM = Month(endDate)
    endD = Day(endDate)
    years = endY - startY
    months = endM - startM
    days = endD - startD
    
    If days < 0 Then
        days = days + Day(DateSerial(endY, endM + 1, 0))
        months = months - 1
    End If
    If months < 0 Then
        months = months + 12
        years = years - 1
    End If
    DateDiffExact = years & " y | " & months & " m | " & days & " d "

End Function

 

[Galaxiom]

declare your parameters as Variant:

It is generally a better practice to declare the parameter datatype and validate the arguments are present before passing them to the function.

Arnelg's function would error internally if passed a string argument.

Microsoft doesn't use Variants for function arguments when a specific datatype is expected.

Variants also use a lot more resources because they can also accept objects.