Calc - Data Entry - default year for date?

I’m trying to do my taxes and entering data for 2015 - it would just save keystrokes if I could set some data defaults for each column, to make data entry more easy. For dates a default year would make it easier. It’s tantalizing since it does a good job of defaulting to the current year, so it seems like I could set a different year even for the whole spreadsheet, since obviously it would confuse my system if I would set my system time to the last year.

If I missed some good documentation where I should have been able to find this then I apologize (but none of it seems to go into enough detail for this that I have seen yet.)

If there is a better way to do the data entry then I would like to know that too (in Office I think I saw a way to make a Form but that was for Access actually!).

Thank you!


I know no option to change the default year (except to change the date of the computer as you said).

A quick workaround would be to type dates without the year (e.g. 6/5), so would be 2016 dates.

Then simply use EditFind & Replace:

  • Search For: 2016
  • Replace With: 2015
  • Other options: check Current selection only (if necessary)

A quick way to check that there is no date for the current year would be to apply conditional formatting on the range: FormatConditional FormattingConditionDate isThis yearApply style: create a new one e.g. with flashy background color.


Thank you! Somehow this has given me an inspiration to use a formula, hmm!

Yes, this would be wise! I tried to use formula, and managed to mess it all up when I tried to sort the rows!

I think it could work to use a formula. Without solving it precisely

IF $A1 > 1/1/2016
$A1 = A1 - one year

Something like $A1 = IF($A1 > DATE(‘01/01/2016’), …, $A1) but I don’t know how to subtract a year.

To subtract one year: =EDATE(A1; -12)

But this solution seems more complicated to me because you have to enter as many formulas that dates to change, unlike a single search and replace…


You’re right, it’s more complicated. (It broke stuff when I tried to sort on the date wrong!)

I used this formula in B1


When you choose “Format Cells” for the column, choose “Date” and insert your desired year in quotes.

Do not follow this advice if you need to have the correct date-value in the cell.!
The actual value of te cell will still contain the unwanted year!
The shown year “2019” would be a fake!
Formats are great in lying!

Thank you for pointing that out, Lupp. This would be a “visual only” fix.