Forum Discussion
Setting recurrent dates in a cell
Hi,
I hope someone can help me.
I have a cell with a date in it based on a formula, Cell C = A1+B1
A B C
| Last Order Date | Order Cycle (days) | Next order date (A+B) |
09/07/2020 | 24 | 02/08/2020 |
I need to ensure that once this date in Cell C is reached and passes 7 days, the cell automatically updates itself to the next calculated value if Cell A hasn't been updated. If Cell A has been updated then Cell C would be past the reached date plus 7 days (based on A+B) and shouldn't trigger an automatic update.
I hope my question is clear.
thank you in advance.
5 Replies
Of course, so much depends on what this is being used for. I'm reading it as a situation where you need to remind yourself to make an order (say for some kind of essential supply, either for home or for work) and need to highlight the situation on a dashboard of some kind when that re-order hasn't happened.
When it has, you change the date in cell A and the urgent reminder is turned off. You give yourself a 7 day period of grace.
If the situation is something more mechanical, by all means, describe it.
How is this for a simpler solution. Just use conditional formatting to turn the Next Order date red if more than seven days have passed without an update to cell A?
Here's the conditional formatting formula
I've attached a spreadsheet that uses this example.
It ends up looking like this when you haven't updated the "Last Order" date. If you have, then the cell appears as a normal (no highlight) date.
Thank you for such a quick response.
This would calculate Cell C only if the button is pressed. My original concern was if I forget to update Cell A then Cell C wouldn't get updated and the next order would be missed. So, I need a backup so that Cell C would update if I forget to update Cell A.
