[Solved] Auto date with different year in Calc

Discuss the spreadsheet application
Post Reply
njmafl
Posts: 1
Joined: Thu Apr 14, 2022 7:29 am

[Solved] Auto date with different year in Calc

Post by njmafl »

I am trying to make a spreadsheet with expenses from last year. However, whenever I put in a month and day, ie. 3/4... Calc automatically converts that date to 03/04/22. Since I am doing expenses from 2021, how to I get the program to auto set the year in the date to 21 as in 03/04/21 instead? Or how can I convert a column of dates (ie: 03/04/22, 4/8/22, 6/30/22, 9/6/22 to 03/04/21, 4/8/21, 6/30/21, 9/6/21)? Thanks!
Last edited by MrProgrammer on Tue Apr 26, 2022 5:18 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OpenOffice 3.3.0 on MacOS 10.14.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to auto date with different year in Calc

Post by Zizi64 »

Solutions:

1.: Type in the full date.
2.: You can substract 365 days (or 366 in a leap year if the dates are earlier than feb.29.) from all of the automatically detected dates. You can do it in the adjacent cells by a formula, then you can copy paste (only) the values back to the original place.
3.: Or you can use the special paste feature with a mathematical operation. Just type-in the value 365 into an empty cell, copy the content of the cell, select the cell range what contains the date values, Paste Special with Math... - Substract option.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: How to auto date with different year in Calc

Post by MrProgrammer »

Hi, and welcome to the forum.
njmafl wrote:How to I get the program to auto set the year in the date to 21 as in 03/04/21 instead?
You can't, unless you tell your operating system to change the system date for your computer to a day in 2021. This forum is for OpenOffice and is not the right place to get assistance with changing the system date.
njmafl wrote:Or how can I convert a column of dates (ie: 03/04/22, 4/8/22, 6/30/22, 9/6/22 to 03/04/21, 4/8/21, 6/30/21, 9/6/21)?
[Tutorial] Text to Columns, Q23/A23

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply