asked on

IIF and years/days

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

thanks

mona4980

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

mona4980

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

mona4980

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

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.

Gustav Brock

Yes.
Open a new module, copy and paste the function, then compile the module and save as, say, basDateUtils.

/gustav

pdvsa

ASKER

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

thank you for the expert help.

Gustav Brock

Great!
It is VBA, Visual Basic for Applications.

/gustav