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

[Nodocify]

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.

[ROSt53]

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.

[Nodocify]

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.

[blindape]

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.

Regards,

John

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

Cheers,

John

[Nodocify]

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