Need help with an "IF" formula

[rb3625]

I have a field named "code" with drop-down choices of "active" or "inactive". I also have date fields for "date down" and "Picked up". I need the total number of days out-of-service calulated and entered in the field named "days o/s". Obviously I need to calculate from the "date down" to today's date as long as the record is "active". However, when "code" becomes "inactive", i need to stop the clock and calculate from the "date down" to "picked up". If someone has the formula for this, please advise.

 

[paulbent]

I'd recommend setting a default value of active or inactive for the code field. Then new records won't have a null value and it's one less thing to test for in the formula.

I would use two calc fields to make it more readable; DaysOut1 will display the result. In the formula '' are two apostrophes to return null.

DaysOut1
If(IsBlank("date down&quot or IsBlank("picked up&quot, '', DaysOut2)

DaysOut2
If(code = 'active', Today() - "date down" , "picked up" - "date down&quot



Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[rb3625]

Thanks to Paulbent, formula works fine.