Setting recurrent dates in a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1584299%22%20slang%3D%22en-US%22%3ESetting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584299%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20can%20help%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20cell%20with%20a%20date%20in%20it%20based%20on%20a%20formula%2C%20Cell%20C%20%3D%20A1%2BB1%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BC%3C%2FP%3E%3CTABLE%20width%3D%22279%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22116%22%3ELast%20Order%20Date%3C%2FTD%3E%3CTD%20width%3D%2241%22%3EOrder%20Cycle%20(days)%3C%2FTD%3E%3CTD%20width%3D%22122%22%3ENext%20order%20date%20(A%2BB)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E09%2F07%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E02%2F08%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20ensure%20that%20once%20this%20date%20in%20Cell%20C%20is%20reached%20and%20passes%207%20days%2C%20the%20cell%20automatically%20updates%20itself%20to%20the%20next%20calculated%20value%20if%20Cell%20A%20hasn't%20been%20updated.%26nbsp%3B%20If%20Cell%20A%20has%20been%20updated%20then%20Cell%20C%20would%20be%20past%20the%20reached%20date%20plus%207%20days%20(based%20on%20A%2BB)%20and%20shouldn't%20trigger%20an%20automatic%20update.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20my%20question%20is%20clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1584299%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584324%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757077%22%20target%3D%22_blank%22%3E%40MoMo71880%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20check%20the%20file%3C%2FP%3E%3CP%3Esolved%20with%20a%20small%20recorded%20macro%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584353%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584353%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20such%20a%20quick%20response.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20calculate%20Cell%20C%20only%20if%20the%20button%20is%20pressed.%20My%20original%20concern%20was%20if%20I%20forget%20to%20update%20Cell%20A%20then%20Cell%20C%20wouldn't%20get%20updated%20and%20the%20next%20order%20would%20be%20missed.%20So%2C%20I%20need%20a%20backup%20so%20that%20Cell%20C%20would%20update%20if%20I%20forget%20to%20update%20Cell%20A.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584418%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584418%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757077%22%20target%3D%22_blank%22%3E%40MoMo71880%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esee%20my%20solution%20now%3C%2FP%3E%3CP%3EI%20think%20a%20table%20would%20be%20better%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584424%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584424%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757077%22%20target%3D%22_blank%22%3E%40MoMo71880%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20is%20this%20for%20a%20simpler%20solution.%20Just%20use%20conditional%20formatting%20to%20turn%20the%20Next%20Order%20date%20red%20if%20more%20than%20seven%20days%20have%20passed%20without%20an%20update%20to%20cell%20A%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20conditional%20formatting%20formula%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1597241149724.png%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212037i8D473028B83B5BA4%2Fimage-dimensions%2F567x275%3Fv%3D1.0%22%20width%3D%22567%22%20height%3D%22275%22%20title%3D%22mathetes_0-1597241149724.png%22%20alt%3D%22mathetes_0-1597241149724.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20spreadsheet%20that%20uses%20this%20example.%3C%2FP%3E%3CP%3EIt%20ends%20up%20looking%20like%20this%20when%20you%20haven't%20updated%20the%20%22Last%20Order%22%20date.%26nbsp%3B%20If%20you%20have%2C%20then%20the%20cell%20appears%20as%20a%20normal%20(no%20highlight)%20date.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1597241230045.png%22%20style%3D%22width%3A%20495px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212038i759D9B2B93921ACD%2Fimage-dimensions%2F495x243%3Fv%3D1.0%22%20width%3D%22495%22%20height%3D%22243%22%20title%3D%22mathetes_1-1597241230045.png%22%20alt%3D%22mathetes_1-1597241230045.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584430%22%20slang%3D%22en-US%22%3ERe%3A%20Setting%20recurrent%20dates%20in%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757077%22%20target%3D%22_blank%22%3E%40MoMo71880%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%2C%20so%20much%20depends%20on%20what%20this%20is%20being%20used%20for.%20I'm%20reading%20it%20as%20a%20situation%20where%20you%20need%20to%20remind%20yourself%20to%20make%20an%20order%20(say%20for%20some%20kind%20of%20essential%20supply%2C%20either%20for%20home%20or%20for%20work)%20and%20need%20to%20highlight%20the%20situation%20on%20a%20dashboard%20of%20some%20kind%20when%20that%20re-order%20hasn't%20happened.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20it%20has%2C%20you%20change%20the%20date%20in%20cell%20A%20and%20the%20urgent%20reminder%20is%20turned%20off.%20You%20give%20yourself%20a%207%20day%20period%20of%20grace.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20situation%20is%20something%20more%20mechanical%2C%20by%20all%20means%2C%20describe%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 DateOrder Cycle (days)Next order date (A+B)

09/07/2020

2402/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
Highlighted

@MoMo71880 

 

 

please check the file

solved with a small recorded macro

Highlighted

@Ramiz_Assaf 

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.

Highlighted

@MoMo71880 

 

see my solution now

I think a table would be better

Highlighted

@MoMo71880 

 

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

mathetes_0-1597241149724.png

 

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.

mathetes_1-1597241230045.png

 

 

Highlighted

@MoMo71880 

 

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.