Ask Your Question
1

Set todays date in a cell but then fix it.

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

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

5 Answers

Sort by » oldest newest most voted
1

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

pierre-yves samyn gravatar image

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

edit flag offensive delete link more

Comments

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

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

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

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

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

LibreGuy gravatar image

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

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

edit flag offensive delete link more

Comments

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 +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!

LibreGuy gravatar imageLibreGuy ( 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!

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

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

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

Comments

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 +0100 )edit
0

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

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

Comments

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

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

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

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 4,113 times

Last updated: Sep 23 '18