Inserting a Fixed Timestamp in Calc

From NeoWiki

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.