IIF and years/days

Posted on 2011-04-28
Last Modified: 2012-05-11
Experts, I have the following that returns the # of days but I need to include years.  Also, if the txtContractBeginDate is Null or "" i woudl like the field to be blank but it still appends "days".
=IIf([txtContractBeginDate]="","",(([txtContractEndDate]-[txtContractBeginDate]) & " days"))

I would like it to be like so (if terms was 100 days):
0 years and 100 days

Question by:pdvsa
    LVL 1

    Expert Comment

    how can you have an end date without a begin date?
    LVL 1

    Expert Comment

    any way you may want to do this with datediff. something like this to find days
    daysofcontracts: IIf(DateDiff("d",[ContractBeginDate],[ContractEndDate])<365,DateDiff("d",[ContractBeginDate],[ContractEndDate]) & " Days","")

    and anything over 365 meaning more than a year it would be this

    Yearssofcontracts: IIf(DateDiff("d",[ContractBeginDate],[ContractEndDate])>365,DateDiff("yyyy",[ContractBeginDate],[ContractEndDate]),"")
    Play with these to get the years if there are any and the days
    LVL 1

    Expert Comment

    to check for null you would do so like nz([contractBeginDate],0)
    LVL 48

    Accepted Solution

    Use the function below to obtain the correct count of years, then finde the remaining days:

    =IIf(Len(Nz([txtContractBeginDate],""))=0,Null,Years(Nz([txtContractBeginDate],Date()),[txtContractEndDate]) & " years and " & DateDiff("d",DateAdd("yyyy",Years(Nz([txtContractBeginDate],Date()),[txtContractEndDate]),[txtContractBeginDate]),[txtContractEndDate]) & " days")

    Public Function Years( _
      ByVal datDate1 As Date, _
      ByVal datDate2 As Date, _
      Optional ByVal booLinear As Boolean) _
      As Integer
    ' Returns the difference in full years between datDate1 and datDate2.
    ' Calculates correctly for:
    '   negative differences
    '   leap years
    '   dates of 29. February
    '   date/time values with embedded time values
    '   negative date/time values (prior to 1899-12-29)
    ' Optionally returns negative counts rounded down to provide a
    ' linear sequence of year counts.
    ' For a given datDate1, if datDate2 is decreased step wise one year from
    ' returning a positive count to returning a negative count, one or two
    ' occurrences of count zero will be returned.
    ' If booLinear is False, the sequence will be:
    '   3, 2, 1, 0,  0, -1, -2
    ' If booLinear is True, the sequence will be:
    '   3, 2, 1, 0, -1, -2, -3
    ' If booLinear is False, reversing datDate1 and datDate2 will return
    ' results of same absolute Value, only the sign will change.
    ' This behaviour mimics that of Fix().
    ' If booLinear is True, reversing datDate1 and datDate2 will return
    ' results where the negative count is offset by -1.
    ' This behaviour mimics that of Int().
    ' DateAdd() is used for check for month end of February as it correctly
    ' returns Feb. 28. when adding a count of years to dates of Feb. 29.
    ' when the resulting year is a common year.
    ' 2000-11-03. Cactus Data ApS, CPH.
    ' 2000-12-16. Leap year correction modified to be symmetrical.
    '             Calculation of intDaysDiff simplified.
    '             Renamed from YearsDiff() to Years().
    ' 2000-12-18. Introduced cbytMonthDaysMax.
    ' 2007-06-22. Version 2. Complete rewrite.
    '             Check for month end of February performed with DateAdd()
    '             after idea of Markus G. Fischer.
      Dim intDiff   As Integer
      Dim intSign   As Integer
      Dim intYears  As Integer
      ' Find difference in calendar years.
      intYears = DateDiff("yyyy", datDate1, datDate2)
      ' For positive resp. negative intervals, check if the second date
      ' falls before, on, or after the crossing date for a full 12 months period
      ' while at the same time correcting for February 29. of leap years.
      If DateDiff("d", datDate1, datDate2) > 0 Then
        intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
        intDiff = Abs(intSign < 0)
        intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
        If intSign <> 0 Then
          ' Offset negative count of years to continuous sequence if requested.
          intDiff = Abs(booLinear)
        End If
        intDiff = intDiff - Abs(intSign < 0)
      End If
      ' Return count of years as count of full 12 months periods.
      Years = intYears - intDiff
    End Function

    Open in new window


    Author Comment

    Gustav:  thanks.... one question:  do I make a module for the function?  I am not too familiar with modules.  I get a #Name after copy and paste of the IIF and dont know if this is because I have not used the function.  
    LVL 48

    Expert Comment

    by:Gustav Brock
    Open a new module, copy and paste the function, then compile the module and save as, say, basDateUtils.


    Author Comment

    whoa that was cool.  Dont understand how it works but it works!

    thank you for the expert help.  
    LVL 48

    Expert Comment

    by:Gustav Brock
    It is VBA, Visual Basic for Applications.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now