Inserting a Fixed Timestamp in Calc

From NeoWiki

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.