How to keep today's Date from updating

Discuss the spreadsheet application
Post Reply
Brevardo
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

How to keep today's Date from updating

Post by Brevardo »

I have a spreadsheet where i entered the formula that displays todays date when I open it. However, if I open the sheet three days later, it will display the new date and i want it to display the original date, kind of like a timestamp. Is there a way I can generate the date and keep it from updating? Your suggestions are greatly appreciated. Thanks
Open Office 3.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Post by Zizi64 »

Just type-in the date as a constant.
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.
Brevardo
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Post by Brevardo »

I have the formula as =TODAY ()
Should I edit this so it will stay and not change when I open the sheet tommorow? Thanks
Open Office 3.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Post by Zizi64 »

I have the formula as =TODAY ()
The formulas always will refresh. Use constants.
You can convert the actual results of the formulas to constants by usage the Ctrl-C / Paste special - Values and Format properties.
Last edited by Zizi64 on Sat Jul 20, 2019 3:49 pm, edited 1 time in total.
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
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to keep today's Date from updating

Post by RoryOF »

This is the old problem set out in Lewis Carroll's "Through the Looking Glass and What Alice Found There"
"The rule is, jam to-morrow and jam yesterday – but never jam to-day."
"It must come sometimes to 'jam to-day'," Alice objected.
"No, it can't," said the Queen. "It's jam every other day: to-day isn't any other day, you know."
"I don't understand you," said Alice. "It's dreadfully confusing!
When you get to tomorrow, it isn't tomorrow, it is today. So the formula does exactly what it should.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Brevardo
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Post by Brevardo »

Im not sure im following you. Im clicking the cell that has the date of July 20, 19 which is the result from the formula =Today () and i dont get anything that says Values or constant? I just get a paste special window that gives me options for selection, operations, skip empty cells and shift cells. I think im either missing something or im not copy and paste special correctly?
Open Office 3.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Post by Zizi64 »

I just get a paste special window that gives me options for selection, operations, skip empty cells and shift cells. I think im either missing something or im not copy and paste special correctly?
The Paste special function in LibreOffice 6.1.6
The Paste special function in LibreOffice 6.1.6
Uncheck the "All" and the "Formulae" options. Check-in the "Numbers", "Text", "Date & Time" options, and you may check-in the "Formats" option too.
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.
Brevardo
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Post by Brevardo »

Okay. I see what you mean as I just did that and it makes sense now. I noticed how the input line now displays the date and not the formula. I'll keep my fingers crossed that the date july 20 19 will also show up tommorow when i open the sheet. Thanks again
Open Office 3.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Post by Zizi64 »

I'll keep my fingers crossed that the date july 20 19 will also show up tommorow when i open the sheet.
The constants never will be changed automatically.


Open Office 3.1
Please update your signature in this forum.
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.
Brevardo
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Post by Brevardo »

Great! Thanks a bunch for the help!
Open Office 3.1
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: How to keep today's Date from updating

Post by F3K Total »

Hello,
another possibility:
  • Menue Tools/Options/OpenOffice Calc/Calculate->Check Iterations
  • CELL B2 Formula:

    Code: Select all

    =IF(A2="";"";IF(CELL("TYPE";B2)="v";B2;TODAY()))
If you now type in anything in cell A2 e.g an "x", the current date appears, and stays in Cell B2.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How to keep today's Date from updating

Post by keme »

You can also select the cell and press function key F2, then F9, then Enter twice.

For me, if it is a single instance, this is the fastest solution. Keyboard shortcuts are more efficient than mouse clicks.
If you need multiple datestamps, you may be better served with a macro, which can be assigned to a keyboard shortcut.

F2 brings up your formula for editing.
F9 recalculates the edit field content, and suggests the value in "autoinput" manner.
First enter accepts the input suggestion to the edit field.
Second enter commits the edit field to the cell.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to keep today's Date from updating

Post by Villeroy »

https://libreoffice.org/ (which is the successor to OpenOffice) has a built-in shortcut Ctrl+Alt+Y which inserts today's date as a constant value into the active cell.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Post by Zizi64 »

has a built-in shortcut Ctrl+Alt+Y which inserts today's date as a constant value into the active cell.
The default shortcut associations depended on the locale settings of the LibreOffice. I have not such shortcut association in my Hungarian LO 6.1.6. I have Shift-Ctrl-. for inserting the actual time as a constant, and I have Ctrl-. for inserting the actual date as a constant.

Check the default associations of the shortcut keys in the Custumize menu.
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.
Post Reply