Order date reminder

[ALM]

I work for a building materials manufacturer and many of our customers provide us with a construction schedule (excel spreadsheet) of their upcoming construction projects. One column includes the “approximate order date.” I have added a column titled “order received” which will be used to list the date we receive the customer’s order. I am looking to add an additional column named “send reminder”. I need help creating a formula which will add yes in the “send reminder” cell IF the “order received” cell is blank and today is on or after the “approximate order date”.

We are creating this as a way to proactively reach out to the client to remind them to send in their order so their project is not delayed.

Any help is appreciated!

[#2]

=IF( ([received]<>"")*([appx date]<=TODAY()) , "YES", "")

[#3]

@ALM 

I've attached a spreadsheet here with a formula that will do what you want. I added in a "GraceDays" capability so that you can, if desired, give your clients a little slack. If you set that number to zero, then the "yes" will appear when the Order Received column is blank and Approx Order date is today or less.

Here's an image that shows the formula in the formula/function bar, but the live spreadsheet is also attached.

[#4]

Can't access a graph

[#5]

@mathetes This is great, thank you! I ran into a couple issues along the way though.  I'm not sure how you have the "grace days" added to your spreadsheet.  When I try to duplicate it on my spreadsheet, my formula just notes the cell number, rather than "GraceDays" as your example shows. (I am very inexperienced with Excel) If I try to eliminate "GraceDays" all together, which I'm perfectly fine with, I see "NAME?" populates the cell. 

Also, some of the "approximate order date" cells are blank as the projects are not underway just yet, so if that cell is blank, a "yes" is populating in the "send reminder" cell. 

So.. is there a way to eliminate the "GraceDays" from the equation and eliminate "yes" from any projects that do not currently have an "approximate order date"?

Thanks again for taking the time to help me out!

Amy

[#6 · Accepted solution]

@ALM 

Use this formula

=IF(A2="","",IF(AND(B2="",A2<(TODAY()-$F$2)),"Yes",""))

This way, if the data in column A is blank, then there is no "Yes' generated, but the rest of the formula is as it was before, except I removed the named reference to "GraceDays" and just refer to the cell F2, which contains the number of days.

[FYI, I had used a slightly advanced feature of Excel, called a "named range," to refer to that cell. It made the formula more "readable," but you can also use the cell reference, F2. And the dollar signs, make it an absolute reference, meaning it stays constant, even as you copy the formula down through multiple rows.]

Since you're a relative beginner, you might find this website useful. https://exceljet.net/search?query=named+range

You can search for various functions, abilities of Excel. There are also videos, and YouTube has lots of useful resources as well.

[#7]

Thank you!!