Inserting a Fixed Timestamp in Calc

From NeoWiki

Jump to: navigation, search

When you insert date and time in a Calc document by using the NOW() function, this data is updated every time you click on Recalculate in the Cell Contents sub-menu of the Tools menu and every time you open your document again after having saved it.
Sometimes, however you need to fix a date, for example to calculate the time which was necessary to perform a task. To do that, you have to use a macro.

In order to know all the available date and time formats:

  • In a Calc document go to the Format menu then choose Cells… and click on the Numbers tab.
  • Select Date and Time in the Category section to display all the available formats.

Recording the macro

  • Choose Record Macro in the Macros sub-menu of the Tools menu.
  • The Record Macro window opens.
  • Select the cell which is to display the fixed timestamp.
  • In the formula bar enter
TEXT(NOW();"MM/DD/YY HH:MM:SS")

to display the date and time in 02/15/08 10:30:35 format, or choose the format you wish.

  • Press the Return key.
  • Choose Copy in the Edit menu, or press the Command-C keys.
  • Choose Paste Special… in the Edit menu, or press the Shift-Command-V keys.
  • In the Paste Special window which appears, uncheck all boxes except Strings.
  • Click OK.
  • In the warning dialog which opens, click Yes.
  • In the Record Macro window, click on Stop Recording.
  • Give this macro a name, for example TimeStamp.
  • Save the macro, following the steps described in Using Macros.
  • You can assign a shortcut or a button to this macro, by applying the methods provided in that article.

Using the macro

To display the elapsed time between Start and End time for a task, open the document which contains the macro. Then

  • Select the cell which has to contain the Start time, e.g. A1.
  • Run the macro TimeStamp.
  • When you want to calculate the time elapsed since the Start time, select an empty cell, e.g. A2, and run the TimeStamp macro.
  • Select the cell which has to show the elapsed time.
  • Apply to this cell the Time format you desire, for example HH:MM:SS.
  • In the formula bar enter
=IF(VALUE(A2)>VALUE(A1);VALUE(A2-A1);"")

and press the Return key.


This article in other languages: Français
Personal tools