Hi,
I have a spreadsheet where I need to have two columns with dates in it, 'Passed' and 'Refresher Required' - the second column needs to be the date three years after the first column.
I inherited this spreadsheet from someone else, who was using this formula:
=IF('First Aid Responder Tracker'!D4="", "", DATE(YEAR('First Aid Responder Tracker'!D4)+3,MONTH('First Aid Responder Tracker'!D4),DAY('First Aid Responder Tracker'!D4)
However, this doesn't automatically update when you sort the columns, so the formula needs to be manually fixed.
I changed it to this formula:
(Please note, it wouldn't let me post this with the 'at symbol' because it kept interpreting it as a link, and I'm too new to post links. But in the formula the '*at symbol*' is supposed to be the actual at symbol)
=IF([*at symbol*Passed]="", "", DATE(YEAR([*at symbol*Passed])+3,MONTH([*at symbol*Passed]),DAY([*at symbol*Passed])))
This formula works fine, and does exactly what I want it to do! The only problem is, when I save and close the spreadsheet, when I open it back up again, the formula has reverted back to the original formula again.
Why is it doing this, and how can I stop it? I'm afraid I'm not an Excel expert, so apologies if the answer to this is obvious!
(For extra info, the spreadsheet has three tabs in it that uses these columns and formula. However, I changed the name of the first column to Passed, Passed1 and Passed2, and changed the formula to match, in the hope that this would fix it. It didn't. There are no errors in the spreadsheet).
Thank you for your help!
Bookmarks