Inserting a Fixed Timestamp in Calc

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 20:55, 21 February 2008 (edit)
Jgd (Talk | contribs)
(New page, translation of the French page Fr:Insérer une date fixe dans Calc)
← Previous diff
Current revision (21:57, 21 February 2008) (edit) (undo)
Lorinda (Talk | contribs)
(Just a little polish; thanks, Jacqueline!)
 
Line 1: Line 1:
-When you insert date and time in a Calc document by using the '''NOW()''' function, this data is updated every time you click on {{menu|Recalculate}} in the {{menu|Cell Contents}} sub-menu of the {{menu|Tools}} menu, or every time you open your document again after having saved it.<br>+When you insert date and time in a Calc document by using the '''NOW()''' function, this data is updated every time you click on {{menu|Recalculate}} in the {{menu|Cell Contents}} sub-menu of the {{menu|Tools}} menu and every time you open your document again after having saved it.<br>
-But you can 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.<br>+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.<br>
In order to know all the available date and time formats: In order to know all the available date and time formats:
Line 9: Line 9:
*Choose {{menu|Record Macro}} in the {{menu|Macros}} sub-menu of the {{menu|Tools}} menu. *Choose {{menu|Record Macro}} in the {{menu|Macros}} sub-menu of the {{menu|Tools}} menu.
*The {{window|Record Macro}} window opens. *The {{window|Record Macro}} window opens.
-*Select the cell which has to display the fixed timestamp.+*Select the cell which is to display the fixed timestamp.
*In the formula bar enter *In the formula bar enter
:: '''TEXT(NOW();"MM/DD/YY HH:MM:SS")''' :: '''TEXT(NOW();"MM/DD/YY HH:MM:SS")'''

Current revision

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