Using DATETIME_DIFF to track expiring and non-expiring products

[Kai_Soremekun]

Hi ~

I have products that expire after 90 days if they haven’t sold.
If they sell they never expire.
I created a field named DAYS TO REMOVAL to track when products that haven’t sold are approaching their 90 day expiration date. I’d also like this field to show an NA for products that have sold and won’t expire.

I used this formula to calculate the Days To Removal
DATETIME_DIFF({Date Uploaded},TODAY(), ‘days’)

This works great for calculating the number of days before removal but it is also giving me negative numbers for products that have sold and won’t expire.

I’d like to have one of two things happens.
IDEAL SOLUTION
When my STATUS field for a product record (a single select type field) gets marked sold the Days To Removal field for that record switches to NA

SECONDARY SOLUTION
When the number of days to removal goes into negative numbers, the result switches to NA instead of showing the negative number.

I have no idea how to make my ideal solution work.

I tried this formula for my secondary solution but it came back with an error.
IF(DATETIME_DIFF({Date Uploaded},TODAY(), ‘days’)<0,“NA”,
IF(DATETIME_DIFF({Date Uploaded},TODAY(), ‘days’)))

Any help is appreciated.

[Danny]

Here’s the solution you’re looking for:

IF(OR(Status=“Sold”, DATETIME_DIFF(TODAY(), {Date Uploaded}, ‘days’)>90), “NA”, 90-DATETIME_DIFF(TODAY(), {Date Uploaded}, ‘days’))

In plain language it says:
If the status is equal to “Sold” or it’s been more than 90 days since the date uploaded, put “NA” in the field. Otherwise, put in 90 minus the difference between today’s date and the date uploaded (i.e. the Days to Removal).

Let me know if you have any questions!

[Kai_Soremekun]

Thank you so much Danny.
That did it. Yaay!

Just a note for beginners like me. I put in the formula Danny gave and kept getting an error message back.
Finally realized the single and double quotes were not in the proper format. Could be tied to me being on a MAC after doing a quick search online. But regardless if a formula doesn’t work , when you paste in the formula erase and retype the quotes.

[Kai_Soremekun]

I realized my formula request wasn’t specific enough.
I think this is clearer.

IN THE {DAYS TO REMOVAL} field I’d like a formula that shows:
If the {Status} field is “SOLD” return “SOLD” (this should not be tied to any number of days. As soon as a product sells and the status field is changed it should show SOLD)

If the {Status} field is “LIVE” or “PROCESSING” return DATETIME_DIFF({Date Uploaded}, TODAY(), 'days’

If the {Status} field is “TO UPLOAD” or “DRAFT” or “DELETED” return “NA”

Note: The Status field is a Single Select field. This formula would cover all the options in that field.

I thought this might require a nested IF statement but every time I mess with it I get an error or the result isn’t what I want.

Again, any help is appreciated. Danny’s explanation of what the formula is saying was so helpful but I’m still trying to wrap my head around how to put them together.

[Kai_Soremekun]

I figured it out with this:

IF(Status=“SOLD”, “SOLD”,
IF(Status=“LIVE”, DATETIME_DIFF(TODAY(), {Date Uploaded}, ‘days’),
IF(Status=“PROCESSING”, DATETIME_DIFF(TODAY(), {Date Uploaded}, ‘days’),
IF(Status=“TO UPLOAD”, “NA”,
IF(Status=“DRAFT”, “NA”,
IF(Status=“DELETED”, “NA”, “NA”))))))

[Danny]

Hey Kai, here’s another way to do it.

IF(
    Status="SOLD",
    "SOLD",
    IF(
        OR(Status="LIVE", Status="PROCESSING"),
        90-DATETIME_DIFF(TODAY(), {Date Uploaded}, "days"),
        "NA"
    )
)

Did you want it to say the days remaining or how many days it has been? This has days remaining, but I noticed yours says how many days its been since upload.

[Danny]

Just for clarity, in your example you can actually leave out the last 3 IF statements and just end with “NA”. It checks everything in order, so that would basically say “if none of these are true, put NA”.

[Kai_Soremekun]

Oh thank you for that. It’s much cleaner. I like to learn how to do it the best way possible.

The days remaining and how many days it’s been are basically the same thing so i guess it could be written either way.
90 days from when a design is uploaded is when it will be taken down.

Thank you so much for your help. I really appreciate it.

K

[Kai_Soremekun]

I’d like to refine this formula:
IF(
Status=“SOLD”,
“SOLD”,
IF(
OR(Status=“LIVE”, Status=“PROCESSING”),
90-DATETIME_DIFF(TODAY(), {Date Uploaded}, ‘days’),
“NA”
)
)

I want to add something for when the 90 day expiration is reached.
When a design has been up for 90 days I’d like to have the status automatically change to “removed”
I tried:
IF({Date Uploaded},’days’>90,Status=“REMOVED”)
and
DATETIME_DIFF(TODAY(),{Date Uploaded},‘days’)>90,Status=“REMOVED”)

How can I incorporate this into the formula I already have?

Thanks for any help

[Justin_Barrett]

I’m assuming that the 90-day check is related to items with a status of LIVE or PROCESSING. In that context, this should do it:

IF(
    Status="SOLD",
    "SOLD",
    IF(
        OR(Status="LIVE", Status="PROCESSING"),
        IF(
            DATETIME_DIFF(TODAY(), {Date Uploaded}, "days") >= 90,
            "REMOVED",
            90-DATETIME_DIFF(TODAY(), {Date Uploaded}, "days")
        ),
        "NA"
    )
)