Ask Your Question

Set todays date in a cell but then fix it.

asked 2016-02-15 18:18:51 +0200

Pete of Ebor gravatar image

Is it possible to get today's date entered automatically into a cell, but then to fix it so that the entered date remains unchanged. I have tried the today() function, but the date changes each time I reopen the spreadsheet. I'm looking to see if there's a quicker method than having to enter the date manually each time.

edit retag flag offensive close merge delete

6 Answers

Sort by » oldest newest most voted

answered 2016-02-15 18:29:57 +0200

pierre-yves samyn gravatar image


You can use the Ctrl+; shortcut to enter the date.

@Lupp also proposed a smart use of circular references here (question was tagged "writer" but it is a Calc solution).


edit flag offensive delete link more


But the Ctrl+; shortcut enters only the current time not date... :-(

gianfrus gravatar imagegianfrus ( 2017-09-19 15:43:19 +0200 )edit

@gianfrus : use Shift+Ctrl+; and extended format...

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-19 15:49:03 +0200 )edit

answered 2019-02-23 21:37:00 +0200

PasteImageJul17 gravatar image

I have the same problem - spent days trying to solve why the neat little code =IF(A1="",TODAY(),A1) for autoentering the date and then fixing it does not work anymore. This has totally spoiled the functioning of a very useful spreadsheet table of mine. Can anyone help? Romjon.

edit flag offensive delete link more

answered 2018-03-10 17:06:52 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Thanks for all the replies, but just using "Ctrl ;" seems to do what I wanted it to do.

edit flag offensive delete link more


Why was this "answer" upvoted? @Pete of Ebor: This should be a comment under an answer, not a separate answer. Also, it sounds like @pierre-yves samyn's answer is what you wanted, so please mark it as correct. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-03-11 06:57:15 +0200 )edit

answered 2018-09-23 12:05:47 +0200

linuxjoeser gravatar image

Unfortunately this does not work anymore in Libreoffice 6. It saves the default date which will not change anymore. A very handy code, which I use a lot, but I do not know how to fix it.

edit flag offensive delete link more


What doesn't work? which code? what steps exactly you do, how it worked before, and what happens now? Please be specific, otherwise, it doesn't make sense.

Mike Kaganski gravatar imageMike Kaganski ( 2018-09-23 12:11:46 +0200 )edit

It's probably related to the keyboard. I didn't report it because I don't use this kind of shortcuts except when a forum request requires it, but already for quite a time the standard shortcut Ctrl+Shift+; didn't work with my German keyboard where the semicolon is Shift+,
On the other hand I tried today also a differently assigned shortcut (Ctrl+q) and it worked.

Lupp gravatar imageLupp ( 2018-09-23 18:43:07 +0200 )edit

answered 2018-09-23 21:33:37 +0200

Lupp gravatar image

(Back to this old thread due to an accidental bump.)

"Just for fun" (not exactly) I combined some pieces of Basic code an polished the result a bit.
Now I can demonstrate how to do this (inserting a fix date), related (fix DateTime, fixTime), and also rather different things by user code called from buttons (formcontrols).

The code contained in the demo is focussing on the evaluation of the calling event thrown by a click, and in specific how to pass parameters to routines called by control events.

I don't expect lots of real-world-applications since documents containing control elements for such purposes should be exceptions, not the rule. The passing of parameters via a property of the source of an event is unfortunately not available for means the UI offers for calling custom code.

Have your fun!

edit flag offensive delete link more

answered 2016-02-16 10:51:09 +0200

LibreGuy gravatar image

updated 2016-02-16 10:52:22 +0200

1) Make sure "Iterations" are enabled:

Go to Calc. Then (in the menu) go to 'Tools' -> 'Options' -> 'LibreOffice Calc' -> 'Calculate' and make sure the 'Iterations' checkbox is enabled.

2) If the fixed date should be in cell A1 ...

Right click cell A1 and choose 'Format Cells'. On the tab 'Numbers' in 'Category' choose 'Date' and click the 'OK' button.

Now enter this formula in cell A1:


Current date will appear in cell A1 and is now fixed :-)

Of course you can extend this formula, for example:

"enter the current date ONLY when a value is entered in cell B1":


Now the current fixed date will only appear in cell A1 if you type something in cell B1.

Enjoy ;-)

edit flag offensive delete link more


The Question was exactly: insert Todays-Date into Cell and fix that date no more no less -- so do it as @pierre-yves samyn has already suggested or choose some other possible shortcut -- no need for this kind of oversophisticated formulas

karolus gravatar imagekarolus ( 2016-02-16 14:27:11 +0200 )edit


No that was not the question. The question was, I quote, "Is it possible to get today's date entered automatically". That indicates the use of a formula and not manual entering!

LibreGuy gravatar imageLibreGuy ( 2016-02-16 15:00:29 +0200 )edit

great stuff @LibreGuy how can I edit this to change row by row eg =IF(A2="";IF(B2="";"";TODAY());A2) for a large number of rows without editing it each time manually? If its possible i'd love to know!

McFaddy gravatar imageMcFaddy ( 2018-11-21 20:32:18 +0200 )edit

Can this be altered to include the time?

pherriot gravatar imagepherriot ( 2019-04-17 10:46:47 +0200 )edit

@pherriot: replace TODAY with NOW

karolus gravatar imagekarolus ( 2019-04-17 22:31:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-02-15 18:18:51 +0200

Seen: 4,672 times

Last updated: Feb 23