Currently I have the following formula in my spreadsheet:
=IF(AND(B7>0,C7>0),NOW(),"ENTER TRUCK NUMBER AND GROSS WEIGHT")
If we have entered a value in b7 and c7 the current time is entered
otherwise a message to enter truck data. This is fine and it works for
the one cell we have the formula in, however as we paste the formula
down the rows of the column with the relative references refering to
b8 and c8 then b9 and c9 etc again there is no problem. But then the
next truck arrives and we enter data for it ( in b8 and C8 then b9 and
c9) and so on BUT then all the previous times in the rows above change
to the current time. I want the formula to deliver the current time
into the cell and then fix it so it won;t change. I have played with
FIXED, HOUR, TEXT functions but got nowhere. Hope someone can help.
JR86

Okay, here goes....

Assuming that this cell is to be located in D7...

=CHOOSE(1+AND(B7>0,C7>0)+(AND(B7>0,C7>0)*(LEFT(D7,3)="ENT")),"ENTER
TRUCK NAME AND GROSS WEIGHT",D7,NOW())

Let's take a look what it does step by step...

- If cells B7 and C7 are zero, you will get the "Enter" message
- Once b7 and c7 are filled, cell D7 checks to see if its own contents
are the "Enter" message, or a date.
- If D7 sees that it currently contains the "Enter" message, it
replaces itself with a date
- If D7 sees that it already has a date, it retains the date.
- If you ever want to remove an entry and start again, just delete the
contents of 'B7' and 'C7, and the "Enter" message will reappear in
'D7'

NOTE:  For the function to work you need to put a check mark in the
"iteration" box of Tools/Options/Calculation.

You do not want to set manual calculation... leave it in automatic.
You can recalculate the spreadsheet until the cows come home... once
the cell has assigned itself a date it will never change.

Cheers,
Blanketpower

JR --

I'm using Office 2000.  Have done the following:
1. set your formula in A7
2. copied it down about 12 cells
3. formatted the A column do that it renders TIME in the following
format: 10/16/02 15:39
4. put a variety of entries in COLS B and C

It works fine for me -- entering the TIME at the point of license
plate and gross weight.

If the previous comment didn't work, let me know two things:
-- does this method work for you?
-- what version of Excel are you using?

Best regards,

Omnivorous-GA

Thank you for all the support so far. None seem to get the result I
need. Omnivirous I am using Excel 97. If you change any of the values
in the other columns, the automatic recalculation of Excel updates the
current time in any cell that has NOW() in its formula.
jr86

I put the thing into a spreadsheet for you since you seem to still be
having trouble. It is at my site:  www.ourcomfyhome.com/Trucks.ZIP

Remember to set the /tools /calculation setting on your spreadsheet to
"iterative" otherwise you will get the annoying "circular calculation"
message.

The first three trucks should come up with the times "10:15, 10:17 and
10:18" which are the times at which I typed them in. Add a few more
trucks, and you will get the current time and date registered for them
without any of the previous entries changing. If you delete a truck
name and weight, the "Enter truck number" message will reappear.

Blanketpopwer I have your copy on my machine and it is working.
However when I duplicate what you have done onto my spreadsheet it
still doesn't work. Could it be that my "Time" column and the columns
which have the weight and truck numbers entered are not next to one
another like your example? My data is entered in b7 and c7 but I am
wanting to put my formula in g7.
This is the first time I have used Google answers and I am very
impressed with the effort to answer my question. Thank you very much
folks.
Can I email my spreadsheet to you Blanketpower?
JR86

Hi,
Then change all D7 to G7 in the formula. It will work.

The changed formula will be

=CHOOSE(1+AND(B7>0,C7>0)+(AND(B7>0,C7>0)*(LEFT(G7,3)="ENT")),"ENTER
TRUCK NAME AND GROSS WEIGHT",G7,NOW())

Hope it helps.