Skip to main content

Formula to show days on market and remove NaN entries

  • March 1, 2022
  • 3 replies
  • 54 views
P

First of all, I am a beginner at creating formulas. I only have experience with with basic math formulas.

I am trying to create a field that show how long a product was on the market. I have two fields, purchase date and sales date. Purchase is the day we bought the items, Sales date is the date we sold it.

I used the datetime_diff formula but I am getting NaN on items that have not sold yet. How do I create a formula that would show days on market for items that have not sold yet. In other words, the items that have been on market for 30 days would show 31 days tomorrow, 32 days the next day, and so on…

Is a formula like that possible? If not, what are my options to remove the NaN appearing in the field?

This topic has been closed for replies.

3 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • March 1, 2022

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!

P
  • Philip_Marsh
  • Author
  • New Participant
  • March 3, 2022

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.

P
  • Philip_Marsh
  • Author
  • New Participant
  • March 3, 2022

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!

Terms & ConditionsAccessibility statement