Excel and dates. Again. But a new wrinkle.

Who knew excel has 2 different time/date systems 'bases'?

I've just inherited the club Vets spreadsheets, and have added some lookup tables for DoB etc.

Now here's a thing...

If you subtract one time from another ( eg a Race Time from a Vets Standard Time ), it doesn't work if the result is negative!

All you get is "######".

This is not just a display formatting issue; you just can't do it!

To fix it, there's an advanced option called 'Use 1904 date system'.

Then, it calculates negative times OK.

BUT....

It it changes the 'base' date of how it stores dates as underlying numbers, and so all the DoB lookups from a spreadsheet which is *not* 1904-based break!

They offset by several years.

So you need to convert the DoB lookup sheet to 1904-based too, so they are both using the same base date.

But you can't just tick that option.

Oh, no.

If you do that, the stored numbers remain unchanged, and the displayed dates change to the wrong ones.

I need the correct displayed text to remain unchanged, but the underlying numbers to be re-calculated.

You need to save out the correct *displayed text* from the DoB column into Notepad. ( Not simply copy the column, because that copies the underlying number which will be wrong under the new system ).

Then change the Date System.

Then copy the text back in from Notepad, to have excel re-generate the underlying numbers in the new system.

And finally it's all working.