Consignment Formula Question: Auto discounting a price based on number of days that have passed

[Judith_Soto]

I track consignment items in Airtable. This is my consignment pricing structure:

Day 0-29: Initial List Price
Day 30-45: Initial List Price minus 15%
Day 45-90: Initial List Price minus 25%
Day 91: Expired

What formula will auto-discount the initial price by the above amounts based on the date when the item sells?
Can the entire cycle of auto-discounts be calculated in one column?
What formula can be used to mark an item “expired” on day 91? - could this formula also be included in the same column as the auto-discount column?

:slightly_smiling_face:

Thanks in advance for any help or insight.

[JonathanB]

You will need a field that shows the initial listing date. This could be either a “Date created” field, which will auto-fill when the record is created, or a date field that you fill manually. Then your formula will use DATETIME_DIFF(NOW(), {Date of listing}, “days”) to give you how many days have passed since the initial listing. Then you wrap that same formula inside IF formulas, i.e.

IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<30, {Price}, IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<45, {Price}-{Price}/100 * 15, IF(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Price}-{Price}/100 * 25, “Expired”)))

This just says "If less than 30 days have passed since the listing, charge full price; otherwise if less than 45 days have passed, take of 15% of the price; otherwise if less than 90 days have passed, take off 25%; otherwise show “Expired”.

[Judith_Soto]

Thanks so much, @JonathanB. That worked like magic!

[JonathanB]

Wonderful! Glad to hear it :grinning_face_with_big_eyes:

[Judith_Soto]

@JonathanB Can I ask for help with a related question?

I want to create a Status column that shows the status of the consignment item as “Active” (not sold), “Sold”, “Expired” (you gave me the formula above), “Paid”, or “Unpaid”

I have fields that I was using to calculate the values. I’m taking a stab at nesting IF-THEN formulas but running into issues.

This is the logic:
If the DateDIFF is <90 days AND the “Sold Date” is blank, the value is “Active”
If the DATEDIFF is <90 AND the “Sold Date” is not blank, the value is “Sold”
If the DATEDIFF is >91 days AND the “Sold Date” is blank, the value is “Expired”
If the DATEDIFF is >91 days AND the “Sold Date” is not blank, the value is “Sold Past Expiration” (this happens sometimes)
If the “Sold Date” is not blank AND the “Payout Date” is blank the value is “Unpaid” otherwise “Paid”

Thanks again!

[JonathanB]

You need to use IF(AND(A, B), “Value1”), IF(AND(C, D), “Value2”, …)) etc i.e.

IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Sold date}=""), “Active”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)<90, {Sold date}!=""), “Sold”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)>91, {Sold date}="", “Expired”, IF(AND(DATETIME_DIFF(NOW(), {Date of listing}, “days”)>91, {Sold date}!="", “Sold Past Expiration”, IF(AND({Sold date}="", {Payout date}="", “Unpaid”, “Paid”))))))))

Nested “IF” formulas can get tricky if you try to do them all at once. I usually lose track of the number of brackets I’ve opened that I need to close at the end! Try doing one part of the formula at once, closing it off, then adding the next IF statement just inside the closed bracket, closing that off, and continuing like that. Each time you put in part of the formula, check that the formula field in Airtable works, and proceed from there :slightly_smiling_face: Close the same number of brackets as you’ve opened. Sometimes I have to go through and count them to make sure it all balances!

[Judith_Soto]

I see what you mean about nesting being tricky. I’m getting an error. I’m troubleshooting but I’m not getting very far. I don’t know much coding.

[Justin_Barrett · Accepted solution]

@Judith_Soto The formula as listed above isn’t formatted as preformatted text, so it contains styled quotes, and styled quotes won’t work in Airtable. If you copied and pasted without fixing the quotes, that will lead to an error. I’ll give you a variant in a bit that fixes that problem, but there are other problems.

In your logic, there are multiple conditions that all trigger on the state of the {Sold Date} field. You have conditions that tie to the elapsed days, as well as conditions for Paid or Unpaid, but those latter two are actually tied to the two “sold” conditions farther up the list; i.e. you want to know the paid/unpaid status of sold items in addition to knowing if they’re sold. With the formula above, these conditions are separate, not connected (plus the last {Sold Date} comparison looked for an empty field, not a filled field). The problem there is that Airtable latches onto the first condition that tests as true, working from the outside in. It won’t return multiple outputs from different levels of the nesting. Looking at the formula above, if the condition that returns “Sold” triggers, you won’t know the paid/unpaid status because that check isn’t done until the innermost level.

Another issue is the day counts that you listed. The two criteria that you indicated are “<90” and “>91”, but “<90” means 89 or lower, and “>91” means 92 or higher. That means that a difference of 90 or 91 days won’t trigger either condition. The second criteria should be “>=90” to ensure that nothing is missed.

Finally, a shortcut way of checking for an empty/filled field is to just use the field name itself. Comparing against an empty string or the BLANK() function is largely unnecessary. (I say “largely” because there’s a small issue with numeric fields, but I won’t get into that here.)

This revised formula has all of these issues addressed:

IF(
    DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
    IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
    IF(
        DATETIME_DIFF(NOW(), {Date of listing}, "days") >= 90,
        IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
    )
)

It might even be safe to assume that if the date difference isn’t less than 90, then it’s automatically >=90, so this even simpler formula might do the job:

IF(
    DATETIME_DIFF(NOW(), {Date of listing}, "days") < 90,
    IF({Sold date}, "Sold - " & IF({Payout date}, "Paid", "Unpaid"), "Active"),
    IF({Sold date}, "Sold Past Expiration - " & IF({Payout date}, "Paid", "Unpaid"), "Expired")
)

[JonathanB]

Thanks Justin. You’re right of course. :slightly_smiling_face:

[Judith_Soto]

Thanks @Justin_Barrett it worked!