What I need is some way to set a static date that doesn't change no matter when you open the file, but I need the date to auto-populate the first time I open the master file.
To explain, the company i work with sends out weekly data files to our clients.
We have a master file where a cell is required to be filled out with a specific day of the week, assuming we are not running some special expedited report.
IE... One report will have a Tuesday date, and that date will always be the next Tuesday.
We "SAVE AS" and rename the file once we paste the client's data.
What I originally setup for the master file was....
The date cell had this formula..... =IF(TEXT(TODAY(),"DDDD")="Tuesday",TODAY(),0) -- what the formula returns when it is FALSE doesn't really matter
I also created a check cell to determine if the date was a Tuesday..... =IF(WEEKDAY(D7)<>3, "TODAY IS NOT TUESDAY! FIX CELL D7!","")
I then realized, this will only work correctly if the file is opened on Tuesday.
If we send that report to our client on Tuesday, and they review it on Wednesday then the formulas would result in showing that there is an error.
I'm looking for a way to force return the Tuesday for that specific week or some formula that will return a static result based on when the file is FIRST opened.
Once I "SAVE AS," and rename the file, the date needs to become static.
If I open it on 4/20/2021, then the date cell will become static, so when I open it on 4/21/2021 the date field will still show 4/20/2021.
I need to be able to open the file months from now and it will indicate the proper Tuesday date for that file.
I may be a bit redundant with some of the info I am providing, but want to get my goal across clearly.
Is this possible?
Bookmarks