Frequently asked questions - Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Documentation Home
• Official Publications
• Other Resources
• FAQ
• HowTo
• Macros
• Docs Development

• FAQ Home
• General
• Writer
• Calc
• Impress
• Draw
• Math
• Base

How to insert a fixed date or time

Problem

The functions TODAY() and NOW() calculate the current date and time automatically but, by definition, these values are automatically recalculated and are not "fixed".

Insertion using the keyboard

The simplest and quickest solution is to use the default key-bindings defined by:

• Ctrl + ; to insert the date,
• ⇧ Shift + Ctrl + ; to insert the time.

In both cases the value contained in the cell is in the format dd/mm/yyyy hh:mm:ss. The default format applied is dd/mm/yy for the date and hh:mm:ss for the time respectively, when only the chosen part is displayed.

Insertion using the mouse

There are two possibilities: creating a dropdown list in the cell or using a "Calendar" control .

• Dropdown list
  1. Select the cell or range of cells concerned and go to Data ▸ Validity
  2. On the Criteria tab, and the Allow listbox, choose Cell Range
  3. In the Source box, type TEXT(TODAY();"JJ/MM/AA")

The formula TODAY() returns the number corresponding to the date since the "origin" date, The formula TEXT() presents this number in the desired format. The user can insert the date with one mouse-click:

Inserting the date with one click in the list

• Calendar control

Display the Form Controls toolbar. In this toolbar, click on "More Controls", then in this new toolbar, choose the tool Date field.

Menu View > Toolbars to select the Form Controls toolbar

1. Draw the control
2. Right-click on it and choose Control to display its properties
3. On the General tab, define the property Dropdown to be Yes
4. Click in the Form Controls toolbar on the second button from the left Design Mode On/Off

From now on, you will have a calendar by clicking on the arrow to scroll through the control.

Click the arrow to display the calendar

Note:

• The input in the calender is not inserted into a cell .
• There is a control called Hour but the latter does not "scroll" like the Date control. It does not therefore permit the current hour to be inserted with a single mouse-click.

Insertion using a timestamp formula

In this example the formula is entered in cell A1 and uses an Iterative Reference to cell B1.

Format cell A1 to the required date format with time.

From the main menu select Tools ▸ Options... ▸ LibreOffice Calc ▸ Calculate.

Click to set the Iterations checkbox and change the Steps to 1 (one)

Click to set the Iterations checkbox and change the Steps to 1 (one).

In cell A1 enter the formula: =IF(B1<>"";NOW();"")

Enter some data in cell B1 and cell A1 will display the time stamp.

Note:

• Editing or changing the contents of cell B1 at a later time will automatically update the time stamp in cell A1.