Forum Discussion
Order date reminder
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!
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.
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.
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
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.
- =IF( ([received]<>"")*([appx date]<=TODAY()) , "YES", "")
- Can't access a graph
