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:
- In the Calc window, choose Tools > Customize…
- Select the Events tab in the Customize dialog box
- Select the Open Document event, then click the Macro… button
- 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.
- Select the insertCurrentDate macro from the Macro name list and click OK.
- 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