Hey @Philip_Marsh! Welcome in!
Here is my revised version of your formula:
IF(
AND(
{Date Purchased},
{Date Sold}
),
ABS(
DATETIME_DIFF(
{Date Purchased},
{Date Sold},
'days'
)
) & ' Days',
IF(
AND(
{Date Purchased} = 0,
{Date Sold} = 0
),
'',
IF(
AND(
{Date Purchased},
{Date Sold} = 0
),
'Pending Sale',
IF(
AND(
{Date Purchased} = 0,
{Date Sold}
),
"
Missing Purchase Date!"
)
)
)
)
And here’s what it looks like in production:
NaN functionally means ‘Not a number’.
So your original DATETIME_DIFF formula was working if all values were provided, however, when you were missing a parameter (one of the dates), then the formula didn’t have anything to return.
Due to the nature of date/time functions, it returned a NaN value instead of just being blank.
In the formula I wrote above, here’s what happens…
If both date fields have values in them, then it will calculate the difference in days between the two dates.
It’s wrapped in a ABS function to account for any negative number of days.
If both fields are not filled, then it will return empty.
If the purchase date is filled, but the sold date is not, then it will return a message indicating that the sale of the home is pending.
Finally, if the property shows sold, but not purchased, then it will return an alert indicating that the data is missing.
Let me know if you run into any issues or have any questions!
Hey @Philip_Marsh! Welcome in!
Here is my revised version of your formula:
IF(
AND(
{Date Purchased},
{Date Sold}
),
ABS(
DATETIME_DIFF(
{Date Purchased},
{Date Sold},
'days'
)
) & ' Days',
IF(
AND(
{Date Purchased} = 0,
{Date Sold} = 0
),
'',
IF(
AND(
{Date Purchased},
{Date Sold} = 0
),
'Pending Sale',
IF(
AND(
{Date Purchased} = 0,
{Date Sold}
),
" Missing Purchase Date!"
)
)
)
)
And here’s what it looks like in production:
NaN functionally means ‘Not a number’.
So your original DATETIME_DIFF formula was working if all values were provided, however, when you were missing a parameter (one of the dates), then the formula didn’t have anything to return.
Due to the nature of date/time functions, it returned a NaN value instead of just being blank.
In the formula I wrote above, here’s what happens…
If both date fields have values in them, then it will calculate the difference in days between the two dates.
It’s wrapped in a ABS function to account for any negative number of days.
If both fields are not filled, then it will return empty.
If the purchase date is filled, but the sold date is not, then it will return a message indicating that the sale of the home is pending.
Finally, if the property shows sold, but not purchased, then it will return an alert indicating that the data is missing.
Let me know if you run into any issues or have any questions!
Thank you so much! This is great. I had no idea all of this was possible. I got it working and seems to have solved my situation.
Hey @Philip_Marsh! Welcome in!
Here is my revised version of your formula:
IF(
AND(
{Date Purchased},
{Date Sold}
),
ABS(
DATETIME_DIFF(
{Date Purchased},
{Date Sold},
'days'
)
) & ' Days',
IF(
AND(
{Date Purchased} = 0,
{Date Sold} = 0
),
'',
IF(
AND(
{Date Purchased},
{Date Sold} = 0
),
'Pending Sale',
IF(
AND(
{Date Purchased} = 0,
{Date Sold}
),
" Missing Purchase Date!"
)
)
)
)
And here’s what it looks like in production:
NaN functionally means ‘Not a number’.
So your original DATETIME_DIFF formula was working if all values were provided, however, when you were missing a parameter (one of the dates), then the formula didn’t have anything to return.
Due to the nature of date/time functions, it returned a NaN value instead of just being blank.
In the formula I wrote above, here’s what happens…
If both date fields have values in them, then it will calculate the difference in days between the two dates.
It’s wrapped in a ABS function to account for any negative number of days.
If both fields are not filled, then it will return empty.
If the purchase date is filled, but the sold date is not, then it will return a message indicating that the sale of the home is pending.
Finally, if the property shows sold, but not purchased, then it will return an alert indicating that the data is missing.
Let me know if you run into any issues or have any questions!
One more question. Instead of it showing pending sale. Is there a way for it to change that to the days that it has been on market to that point.
for example, today it has been on the market for 28 days, tomorrow it will be 29 days, and so on. when it sells we will enter the sales date which would stop the progression of days.
My goal is to be able to see how long sold items were on the market AND also how long the items that are still for sale have been on the market. AND be able to see the average as a summary at bottom of column.
Not sure if all that is possible.
Hope that makes sense. thanks again!
