Page 1 of 1

How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 1:30 pm
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

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 1:36 pm
by Zizi64
Just type-in the date as a constant.

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 1:50 pm
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

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 2:19 pm
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.

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 2:33 pm
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.

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 5:14 pm
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?

Re: How to keep today's Date from updating

PostPosted: Sat Jul 20, 2019 6:56 pm
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?


PasteSpecial.png
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.

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 12:06 am
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

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 12:36 am
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.

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 2:23 am
by Brevardo
Great! Thanks a bunch for the help!

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 9:55 am
by F3K Total
Hello,
another possibility:
  • Menue Tools/Options/OpenOffice Calc/Calculate->Check Iterations
  • CELL B2 Formula:
    Code: Select all   Expand viewCollapse view
    =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

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 10:20 am
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.

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 12:01 pm
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.

Re: How to keep today's Date from updating

PostPosted: Sun Jul 21, 2019 12:11 pm
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.