Date Calculator

Topic

New Reply

I am trying to find the difference between two dates. I need the difference in Years, months, and days all in the same calculation. I can’t get the datediff() function to do what I want. Does anyone have a workaround I can use. Thanks

Reply | Quote

Replies

Edited (several times – and then several times again) by MarkJ to fix a few small errors in the code

You could write a custom function (or functions) to accomplish your purpose. You’ll inevidably find yourself needing the DateDiff() function in the process.

You’ll probably want to so something like the following: (it may not be perfect, but it’s in the right direction)

Function TimeBetweenDates(Startdate As Date, EndDate As Date) As String
    'Written by Mark S. Johnston (Markjsc@yahoo.com)
    '5/30/2001
    
    'Switch the dates in case the StartDate comes
    'after the EndDate
    If Startdate > EndDate Then
        Dim tmpDate As Date
        tmpDate = EndDate
        EndDate = Startdate
        Startdate = tmpDate
    End If
    
    
    Dim YearDiff As Long
    Dim MonthDiff As Long
    Dim DayDiff As Long
    
    'Calculate Year
    If Format(Startdate, "mm/dd")  0 Then
        If Format(Startdate, "mm") > Format(EndDate, "mm") Then
            MonthDiff = 12 - Abs(DatePart("m", EndDate) - DatePart("m", Startdate))
        Else
            MonthDiff = Abs(DatePart("m", EndDate) - DatePart("m", Startdate)) - 1
        End If
    Else
        If Format(Startdate, "mm") > Format(EndDate, "mm") Then
            MonthDiff = 12 - Abs(DatePart("m", EndDate) - DatePart("m", Startdate))
        Else
            MonthDiff = Abs(DatePart("m", EndDate) - DatePart("m", Startdate))
        End If
    End If
    
    
    'Calculate Day
    If MonthDiff = 0 Then
        DayDiff = Abs(DatePart("y", EndDate) - DatePart("y", Startdate))
    Else
        DayDiff = Abs(DatePart("d", EndDate) - DatePart("d", Startdate))
    End If
  
    'Return Results
    TimeBetweenDates = YearDiff & " Years, " & _
                       MonthDiff & " Months, " & _
                       DayDiff & " Days"
End Function

HTH

Reply | Quote

Thanks for the Help

Reply | Quote

Did it work for you?? I whipped it up in a hurry and didn’t test every possible scenario. Let me know how it does.

Reply | Quote

It worked real good. I did delete the -1 from the calculate Day section. I tested it by putting in May 1, 2001 and May 31, 2001 and came back with 29 days. With the -2 deleted it seems to be working great. Thanks

Reply | Quote

Hi Larin,
Thanks for catching the error on the Day calculation. That’s what I get for not double-checking my work! I also found another little bug in the first If statement (tmpDate = EndDate). I updated the original post with the fix.

Reply | Quote

Thanks,

I have been doing some more work with it and found another problem. If the start date (we’ll call it birthday) has already happened for the current calendar year then it seems to work fine. If the Birthday has not happened yet during the current calendar year. It calculates wrong. I am checking a couple of things to see what the problem is.

Reply | Quote

DOH!!
Ah HA!! I think I have it. Check the original post for the change (in the Year section).

Reply | Quote

It’s getting real close.

If I enter a start date of 6/1/2000 and an end date of 5/1/2001 I get a result of 1 month. It should be 11 months.

The year is working great now and I haven’t checked the day to see if the above dates mess it up also.

Thank you so much for your help.

Reply | Quote

Woah – I’m glad I’m not getting paid for this one. I really should test these things more thoroughly before posting them.
I’m still working on it, and trying to test a little better this time. I’ll post the (hopefully) finished work back shortly.

Thanks for your patience.

Reply | Quote

Thank you so much. It seems to be working great.

Reply | Quote

I believe that this expression will work:
Date of Birth:
=IIf(DatePart(“m”,Now())>=DatePart(“m”,[DoB]),DateDiff(“yyyy”,[dob],Now()),DateDiff(“yyyy”,[dob],Now())-1)

Reply | Quote

Ok – I think it’s finished (but I wouldn’t bet much money on it ). As before, see the original post for the latest update.

Thanks for your patience.

Reply | Quote