Case / If for date calculation

[sarahw]

Hi all, I am new to FM and new here.. learning as I go, so far so good.. though I am stuck on one calculation.

My question is how to write this calculation when one of the fields included in the calculation is empty.

I am trying to accomplish a calculation of "days held" for assets that we purchase and then resell.

Take the lesser of CurrentDate or DateSold, and subtract the Purchase Date.

If DateSold is empty, then CurrentDate - Purchase Date.

The result is the number of Days Held.

I think I have to use Case but I am not sure. Here is what I have:

Case (

IsEmpty ( Date_Sold ) ; Get ( CurrentDate );

If ( Date_Sold < Get ( CurrentDate ); Date_Sold - Date_Purchased; Get ( CurrentDate ) - Date_Purchased )

)

Am I at all on the right path here? any help appreciated. Thank you.

[bruceR]

One way:

Date_Sold - case( isEmpty( Date_Purchased); Get ( CurrentDate ) ; Date_Purchased )

[LaRetta]

And another way ...

Date_Sold - Min ( Date_Purchased ; Get ( CurrentDate ) )

... be sure this number calculation is set to 'do not store...' in Storage Options

[LaRetta]

Wait a minute ... I just looked at Bruce' calculation but that doesn't seem like what you want. Try instead:

Min ( Date_Sold ; Get ( CurrentDate ) - Date_Purchased )

... the item was purchased before it was sold. You want the days duration (up through the current date) if not yet sold; otherwise use sale date.

[bruceR]

You're right!

[sarahw]

Thanks! this works great, I had to adjust the position of the parentheses a bit - below is the final formula I used. Many thanks for your help!

[color:green]Min ( Date_Sold ; Get ( CurrentDate )) - Date_Purchased

[Lee Smith]

I believe you meant your reply to be addressed to LaRetta. If you use the Quick Reply at the bottom of the page, the Forum assumes you are making your reply to the last person to post prior to your Reply.

The surest way to ensure that your reply is addressed to the person you are intending it for, is to use the Reply button in the body of the post you are replying to.

Lee

[LaRetta]

:blush2: That's what I get for typing in hurry!

Happy birthday, Lee! :party:

[Lee Smith]

:

Thank you