Quote:
Originally Posted by ArviLaanemets
No formula can refer to same cell where it is. So yes, in case you want to overwrite the value without storing original one somewhere, then VBA is only solution.
When you want really to do this, then consider a procedure which is run manually or is called by open event of workbook at start of every year. When you use e.g. workseet change event, then the code is run every time you edit anything in any cell on worksheet. This may slow your workbook down considerably!
|
That's a good point. I thought it would be possible to run a macro a single time when opening the worksheet.
Quote:
Or user simply enters formula instead of date into cell. Something like
Code:
=DATE(YEAR(TODAY(),10,22)
|
I am actually using that formula elsewhere in the sheet. It is great for many purposes, but it will not work here. Take the example above of June 7. This formula would change June 7, 2019, to June 7, 2020, as soon as today's date hits January 1, 2020. That means the user loses half a year of reminders for their work. Although we do have some dates based on the calendar year (and I handle those with other formulas in my worksheet), several of our reminders are not dependent on the calendar year but on a 12-month cycle that begins with the intake date, which could be any date during the year.
Updating the year in the target cell is a way of allowing the user to continue using the worksheet the next year without having to manually update the dates. As an alternative, I could turn the cell red when the year has passed so that the user knows to manually update, but feedback in my department is that they would like the dates to update automatically.