Calc - Data Entry - default year for date?

Alameattemptatanonymity · February 8, 2016, 5:41am

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!

PYS · February 8, 2016, 6:23pm

Hi

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 Edit▸Find & 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: Format▸Conditional Formatting▸Condition▸Date is▸This year▸Apply style: create a new one e.g. with flashy background color.

Regards

Alameattemptatanonymity · February 9, 2016, 4:25pm

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

Alameattemptatanonymity · March 27, 2016, 3:51pm

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

Alameattemptatanonymity · February 9, 2016, 4:27pm

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.

PYS · February 10, 2016, 9:13am

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…

Regards

Alameattemptatanonymity · March 27, 2016, 3:52pm

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

=DATE(YEAR(A1)-1, MONTH(A1), DAY(A1))

Ken9087567 · July 11, 2020, 3:09pm

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

Lupp · July 11, 2020, 6:18pm

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!

Ken9087567 · July 11, 2020, 7:16pm

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