How to set up If Then formula that includes date values

AbbeyOJ

I have been trying to create a formula that uses two data points that will have a different status based on if it is before or after it's need by date.

This is what I currently have:

=IF(([Quantity Ordered]<>[Quantity Shipped],[Quantity NBD]>(today), "Open"))

I am not sure what is making it so that the formula does not work.

AbbeyOJ

I also have the following formulas:

=IF(([Quantity Ordered]<>[Quantity Shipped],[Quantity NBD]<(today), "Past Due"))

=IF(([Quantity Ordered]<>[Quantity Shipped],[Quantity NBD]>(3+today), "Upcoming"))

Jake Kenyon

Hey Abbey!

Just to make sure I'm understanding the question, are you wanting to have both criteria met for the status to change? Additionally, I'm assuming quantity NBD is a date, is that correct?

In a written format, it sounds like you're saying if the quantity ordered does not equal the quantity shipped and quantity NBD (date) is in the future, show the status as updated, if quantity NBD in the next three days show it as upcoming, and if quantity NBD is in the past mark as past due.

If this is per line item that you are trying to generate a status for, using @row following each of your references should help your formulas work as expected. I added an option for "Received" if the quantity ordered does equal the quantity shipped and this is the formula I came up with:

=IF(AND([Quantity Ordered]@row <> [Quantity Shipped]@row, [Quantity NBD]@row < TODAY()), "Past Due", IF(AND([Quantity Ordered]@row <> [Quantity Shipped]@row, [Quantity NBD]@row <= TODAY(+3)), "Upcoming", IF(AND([Quantity Ordered]@row <> [Quantity Shipped]@row, [Quantity NBD]@row > TODAY()), "Open", "Received")))

If you set this as a column formula it should show you the correct status for each line item, let me know if this works for what you are looking for!

AbbeyOJ

Hello Jake!

Thank you for this but it seems to still be showing up as "Unparseable", That is what I would want it to do but I am unsure on why it isn't working.

Jake Kenyon

Hmmmmm, sorry to hear that. If you're still working on it, could you send a screenshot of how you have this setup? And you have the quantity NBD set as a date, correct? I had it working on my end, you could always try it in a new sheet to make sure you don't have something set up oddly in your current configuration.