At 08:47 13/04/2007 +0300, Andis Lazdinsh wrote:
I'm planning to use openoffice.calc in time studies of forest
harvesting, forwarding and other operations. I'm working on the
model, where I can insert number of operation (1, 2, 3, ...20) in
one column, starting time in second column and finishing time in
third column. As some operations occur at the same time, I need
separate column, where I mark, that operation is finished, so I have
four columns for data entering and fifth column for comments.
My problem is how to insert fixed time (HH:MM:SS) in time columns as
soon as I enter number of operation and mark that operation is
finished. Please, give me advice, how to do it using macros or
combination of build in functions and macros. The main point is to
have fixed time.
Your finishing times are in the third column and your finishing marks
in the fourth, so let's suppose these are columns C and D
respectively. So you want to be able to insert the mark in column D
and see the time when you did this appear magically in the
corresponding cell in column C. Do I understand correctly?
The formula you need for C1 would be:
=IF(ISBLANK(D1);"";NOW())
This tests D1 to see if it is blank (that is, empty). If it is, C1
remains empty; if it is not, then the current time ("NOW") is
inserted in C1. So when you enter something into D1, the time
magically appears in C1.
Note:
1. The default time format includes the date as well as the time, so
you will have to format column C as HH:MM:SS to achieve what you want.
2. Although you will get the correct time in C when you enter
something in D, all these times will be recalculated when you make
other changes. So after you have entered the value in D, you must
freeze the time in C before you do anything else. To do this, copy
the contents of just the relevant cell in column C, and then paste it
straight back in, but using "Paste Special" instead of
"Paste". ("Copy" and "Paste Special" are both available from
right-click in the context menu.) In the Paste Special dialogue box,
first remove the tick from "Paste all" if necessary. Then make sure
that "Numbers" is ticked and "Formulae" is *not* ticked. Now you
have the finishing time as a constant rather than as a formula, and
it won't change when you make other alterations or additions.
Unless anyone knows a neater method ...
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
