We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

LO 4.3 calc template to fill in date but never change it after it has been filled in [closed]

asked 2014-10-05 01:44:50 +0200

Nodocify gravatar image

updated 2016-03-04 19:09:45 +0200

Alex Kemp gravatar image

I am creating a sales receipt template in calc 4.3. This template at the moment uses the =TODAY() function to fill in the date. The problem with this is that the function is carried over when someone saves a documents from the template. This then updates the date if the file is opened the next day. What I am looking for is a way for the date to be filled in automatically when the template is opened but then never changed. This is a sales receipt, keeping the sale date is kinda important. Any suggestions on how to do this?

One 'solution' that I have come up with is to have a cell outside of the printable area using the =TODAY() function that the employee would copy and then Paste Special into the date field. The problem with this route is getting the employees to remember to do this. So I am back to my original problem.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 19:10:04.763192

2 Answers

Sort by » oldest newest most voted

answered 2014-10-06 11:04:12 +0200

blindape gravatar image

updated 2014-10-08 04:21:48 +0200

TODAY is brilliant when you need formulas to constantly recalculate based on the current date. However there are many scenarios where once the date has been entered, you would like it to become static

There is a keyboard shortcut to enter a static version of the current date (Ctrl + ;) However, this would still require users to remember to carry out this step.

Unfortunately, to have a static version of the current date automatically applied to a new instance of your receipt template would require a macro.



As you requested, I have added a simple macro below which you can use to enter the current date just once:

Sub insertCurrentDate
Dim oDoc as Object
Dim oSheet as Object
DIm oCell as Object
    oDoc = ThisComponent 
    oSHeet = oDoc.Sheets.getByName("Sheet1")
    oCell = oSheet.getCellRangeByName("B3") 
    If oCell.Type = EMPTY Then oCell.Value = Now
End sub

Basically the macro checks whether the cell B3 is empty and uses the Now function to add the current date and time to the cell when it is empty. If you have any questions about the code let me know. Modify the settings such as the sheet and cell names to fit your requirements.

To make this macro run automatically first you need to add the code to a macro module in your workbook. Once the code has been added, the second step is to set up an event that triggers the macro to run. There are many events such as opening, saving, closing and printing that can all trigger macros.

To run the macro, every time the file is opened carry out the following steps:

  1. In the Calc window, choose Tools > Customize...
  2. Select the Events tab in the Customize dialog box
  3. Select the Open Document event, then click the Macro... button
  4. In the Library list of the Macro Selector dialog box, navigate to the module that contains the macro code. This should be stored under your file name.
  5. Select the insertCurrentDate macro from the Macro name list and click OK.
  6. Click OK in the Customize dialog box.

Your insert current date is now ready to run whenever the file is opened. Of course once the date has been entered the first time, the date cell is no longer empty and the command that enters the date into the cell is skipped.

Download the file at the following link to see a working example. CurrentDateMacro.ods



edit flag offensive delete link more


Any suggestions where I should look for creating such a macro?

Nodocify gravatar imageNodocify ( 2014-10-06 19:15:11 +0200 )edit

answered 2014-10-05 03:11:26 +0200

ROSt52 gravatar image

Most likely "Auto Calculate" is not switched on.

Tools > Cell contents > AutoCalculate

Quick test: Open a file based on your template and if the date is not updated press F9 = recalculate. If F9 results in the right date, switch on AutoCalculate.

edit flag offensive delete link more


I think that you misread my question. I want the date to become static. I do not want it to change/recalculate after the initial.

Nodocify gravatar imageNodocify ( 2014-10-06 19:14:06 +0200 )edit

Question Tools

1 follower


Asked: 2014-10-05 01:44:50 +0200

Seen: 608 times

Last updated: Oct 08 '14