Spreadsheet question

The auto fill dates (unlike dates computed from the previous cell etc) are ultimately just hard coded cell contents - they do not remain linked in any way - so changing one will have no effect on the others.

Perhaps I am not understanding what you are saying?

For a whole number of reasons that seems like a bad idea...

I didn't express it well. What I meant was: autofill gives a block of dates with the desired year, but sometimes it's necessary to overtype the autofilled date with another day and month (several entries for the same day, for example) and the year then changes back to 2023 because there seems to be no way to force a fixed year to be part of the cell (or page) format.

Yes, changing the system clock needs to be done with care. I vaguely remember a story from my working days during the run-up to Y2K - one of the clients of my employer decided to test their systems by changing the system date ... but they didn't think of disconnecting the backup systems, which got thoroughly confused and a lot of data was lost for a while.

I have been using spreadsheets for at least 25 years (early Lotus 1-2-3) and had never realised that :-) Just tried, using Libreoffice, and it works!

I can see if you type in a new date on top of an existing cell, and only type (say) 1/5, then it will assume you mean 1/5/23... if you type

What you can do it write a on change event handler to inspect changes to the sheet and "catch" edits to dates....

Like this:

In my demo sheet, I designated a cell for a "base year" - I used B1

I then added the following script to the sheet (Extensions->Apps Script):

function onChange( e ){ let currentSheet = e.source.getActiveSheet() let currentCell = currentSheet.getCurrentCell()

if( currentCell.getNumberFormat() == "dd/MM/yyyy" ){ let baseYear = currentSheet.getRange( "B1" ).getValue() if( baseYear != "" ){ let editedDate = new Date( currentCell.getValue() ) editedDate.setFullYear( baseYear ) currentCell.setValue( editedDate ) } } }

Then I added a trigger to the sheet to call that onChange function in response to a change event on the sheet.

Each time you complete a change to the sheet, it grabs the current cell, and checks to see if it contains a date field in numeric format. If it does it reads the date and the base year, it then coerces the year part to whatever year you have set in your base year cell. If you don't want that behaviour at any given time, just clear the base year cell.

Note I collected the reference to the sheet from the event object passed in - you could use a hard coded sheet access if preferred by settings currentSheet to something like:

let currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of sheet")

You could also filter on more criteria - say only taking interest in changes on a particular sheet or range etc.

Yup problems with versioning and file time stamps is a big problem. It will bit in other more mundane ways - like you will get errors connecting to secure web sites because there is too much error in in the clock.