How to keep today's Date from updating

Discuss the spreadsheet application

How to keep today's Date from updating

Postby Brevardo » Sat Jul 20, 2019 1:30 pm

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
Brevardo
 
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Postby Zizi64 » Sat Jul 20, 2019 1:36 pm

Just type-in the date as a constant.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8492
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Postby Brevardo » Sat Jul 20, 2019 1:50 pm

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
Brevardo
 
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Postby Zizi64 » Sat Jul 20, 2019 2:19 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8492
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Postby RoryOF » Sat Jul 20, 2019 2:33 pm

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.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29796
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: How to keep today's Date from updating

Postby Brevardo » Sat Jul 20, 2019 5:14 pm

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
Brevardo
 
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Postby Zizi64 » Sat Jul 20, 2019 6:56 pm

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8492
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Postby Brevardo » Sun Jul 21, 2019 12:06 am

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
Brevardo
 
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Postby Zizi64 » Sun Jul 21, 2019 12:36 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8492
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to keep today's Date from updating

Postby Brevardo » Sun Jul 21, 2019 2:23 am

Great! Thanks a bunch for the help!
Open Office 3.1
Brevardo
 
Posts: 15
Joined: Sat Jul 20, 2019 12:58 pm

Re: How to keep today's Date from updating

Postby F3K Total » Sun Jul 21, 2019 9:55 am

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
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 935
Joined: Fri Dec 16, 2011 8:20 pm

Re: How to keep today's Date from updating

Postby keme » Sun Jul 21, 2019 10:20 am

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
keme
Volunteer
 
Posts: 3272
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How to keep today's Date from updating

Postby Villeroy » Sun Jul 21, 2019 12:01 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27311
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to keep today's Date from updating

Postby Zizi64 » Sun Jul 21, 2019 12:11 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8492
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 44 guests