Results 1 to 13 of 13

Thread: Date Calculator

  1. #1
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Calculator

    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

  2. #2
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    Thanks for the Help

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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.

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  4. #4
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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.

  6. #6
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    DOH!! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    Ah HA!! I think I have it. Check the original post for the change (in the Year section).

  8. #8
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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.

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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)<pre>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") <= Format(EndDate, "mm/dd") Then
    YearDiff = Abs(DateDiff("yyyy", Startdate, EndDate))
    Else
    YearDiff = Abs(DateDiff("yyyy", Startdate, EndDate)) - 1
    End If

    'Calculate Month
    If DateDiff("d", DatePart("d", EndDate), DatePart("d", Startdate)) > 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</pre>


    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    Woah - I'm glad I'm not getting paid for this one. I really should test these things more thoroughly before posting them. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    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.

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    Ok - I think it's finished (but I wouldn't bet much money on it <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>). As before, see the original post for the latest update.

    Thanks for your patience. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  12. #12
    New Lounger
    Join Date
    May 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

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

  13. #13
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculator

    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)
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •