Subtracting dates

[Anonymous]

Hi

I need some help with calculating close days for cases

If the cases are still open (Case Completed Field) the Disciplinary
Interview Outcome Date = 9999-12-31.

So for open days on open cases I use this formula:

=num(Today())-num([Date of Alleged Offence])

Close days on close cases I use this formula:

=num([Disciplinary Interview Outcome Date])-num([Date of Alleged Offence])

• I would like to use one formula for open and closed cases to
  calculate Case Days. Could you assist with a possible solution or if
  statement?

  My problem is that it shows 2 917 832 days
for open cases on my close case days formula

[PrashantSangle · Accepted solution]

Hi,

NUM(Date#('9999-12-31','YYYY-MM-DD')) is equal to 2958465

therefore you can try like

if(num([Disciplinary Interview Outcome Date])=2958465,

num(Today())-num([Date of Alleged Offence]),

num([Disciplinary Interview Outcome Date])-num([Date of Alleged Offence])

)

Regards