Subtraction result and Average field

[Xaqtly]

Sorry subject isn't very descriptive, not enough room.

I've got a report that subtracts one date from another date, and puts the result in a very simple calculation field. It's basically just date_2 - date_1.

Additionally I've got a summary field in the footer that gets the average days from that calculation field.

My problem is when we get a row that only has one date added, and not both. When this happens I get a question mark in the calculation field, and apparently what Filemaker is doing is just plugging in the number 733533. At least that's what the field shows when I export it to Excel.

So obviously this completely throws my Average field off, as it starts showing things like -73554.6 days instead of maybe 12 or 15. My question is, is there a way to make the calculation field just show zero instead of 733533 when there's only one date instead of two? I really just want the summary field to ignore the calculation results from the "only one date" instances.

[comment]

apparently what Filemaker is doing is just plugging in the number 733533

No, it's subtracting from date_2, so the result is date_2 as a number (which is the number of days elapsed since 1/1/0001).

is there a way to make the calculation field just show zero

I don't think you want to do that, since these zeros will be included in the average. But you can easily make the result empty by changing your calc to =

Case ( date_1 and date_2 ; date_2 - date_1 )

[Xaqtly]

That seems to have worked perfectly, thanks a lot. If you don't mind though, would you mind explaining in noob speak why it worked? Like, what does the Case argument have to do with it? I'm still trying to figure out how calculations work.

[comment]

The Case() function is very similar to If(). Here, it tests if both dates exists, and if so it returns their difference. Otherwise there is no result (i.e. the field is left empty).

[Xaqtly]

That makes sense. Thanks!