[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


IIF and years/days

Posted on 2011-04-28
Medium Priority
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
  • 3
  • 3
  • 2

Expert Comment

ID: 35487865
how can you have an end date without a begin date?

Expert Comment

ID: 35488025
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

Expert Comment

ID: 35488061
to check for null you would do so like nz([contractBeginDate],0)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 35489378
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

ID: 35490976
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 52

Expert Comment

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


Author Comment

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

thank you for the expert help.  
LVL 52

Expert Comment

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


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

873 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