Spreadsheet question

A quick check with Excel reminds me that if you type a full date in a cell, then each cell below that defaults to the same year. So, eg:

I use LibreOffice Calc instead of Excel, so this probably doesn't apply ...

When I type in the dd/mm part and let it guess the yyyy part, I pretty sure it takes the guessed part as being correct and fixed thereafter, so if isn't, you have to go back and edit them individually.

=====

I had a weird spredsheet issue yesterday (tax return time) exported a set of transctions from bank as a .csv file, imported into Calc, tried to sum the column, it gave zero, because the individual cells were formatted like " £1.23" and treated as strings.

So did a search and replace from " £" to "" and tried to sum the column again, still zero.

Then did a formula of =D1+D2...D11+D12 instead of a =SUM(D1..D12) which worked ... huh?

I just checked that in Excel and Google sheets (which is where my spreadsheet is) and the default remains the current year, rather than the one I enter in the previous cell

This is a Google sheet (but the same behaviour appears to be true of Excel), I can't find a way to change the default year.

My suspicion is that there is an implicit cast in the a+b+C that doesn't happen in the 'sum(a..c)' expression

Pretty sure it's just "the year today is in"

I've just remembered how I did it previously: change the system clock to the desired year. Simples!

I was about to suggest that! Don't forget to change it back when you've finished.

One way that might work for you:

If you enter one date - say 1/1/22, hit enter, then click back on that cell. Notice that it puts a line boarder round the cell with a small block on the bottom right corner. Drag that block down as many rows as you want.

It will auto fill consecutive dates in the column. Now fill in your entries as required against the date, and delete any rows you don't need.

Or just type in 23/12/22 or 23/12/2022 and make sure the cell is formatted to only show the part that you want.

Google sheets is rubbish. Brian

Hmm, yet another thing that blind people cannot do then. I am realising why I hate spreadsheets generally. Brian

That is a bodge though. I'm sure there is a way to backdate entries somewhere, and then have the sheet redo the calculation. Brian

How would that help would this not result in things in the wrong year? Brian

One way to do it is to use a date input column (say column B) then calculate a column (say C) from that which forces the year to the year you want.

eg. =DATE($C$1,MONTH(B2),DAY(B2))

where $C$1 contains the year you want.

When you're done, copy the calculated column C and paste values over the input column B and delete column C.

Changing system date might have side effects.

Based on what criteria?

I would say it is one of the best products of its type out there. Easy to use, full featured, and massively scriptable which makes building complete applications on top of it very easy.

Add on to that multiple user concurrent access to the sheets and easy and secure document sharing and it is hard to beat.

I tried that, but having got a block of 2022 dates they become 2023 if I just (for example) then type in 2-12. Changing the system clock seems to be the only way to do what I need

Yes, clearly that's the straightforward way but people who normally just enter the day and month keep forgetting to add the year and that causes great confusion later when they meant 2022 but it defaults to 2023.

Aha! Great minds (etcetera) ... that's the answer that SWMBO came up with but I convinced her it was simpler to change the clock. The only side effect seems to be if it's not corrected before trying to log on again (and, of course, back-up software would be confused if it was allowed to run)