# Set todays date in a cell but then fix it.

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 close merge delete

Sort by » oldest newest most voted

Hi

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).

Regards

more

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

( 2017-09-19 15:43:19 +0100 )edit

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

( 2017-09-19 15:49:03 +0100 )edit

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.

more

( 2018-03-11 06:57:15 +0100 )edit

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.

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.

( 2018-09-23 12:11:46 +0100 )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.

( 2018-09-23 18:43:07 +0100 )edit

(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.

more

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.

more

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:

=IF(A1="";TODAY();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":

=IF(A1="";IF(B1="";"";TODAY());A1)

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

Enjoy ;-)

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

( 2016-02-16 14:27:11 +0100 )edit

@karolus

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!

( 2016-02-16 15:00:29 +0100 )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!

( 2018-11-21 20:32:18 +0100 )edit