We use a spreadsheet to log incoming inspections of products. I want to
monitor how long it takes between receipt and inspection; receipt is logged
into a different spreadsheet with date and time. I want to have the
inspection worksheet automatically date stamp when an inspection is
completed; I want to do this using IF(ISBLANK(xx),"",NOW()). The problem is
that everytime the spreadsheet is opened the time is recalculated. I can
turn off recalc, but at times it is necessary to do a recalculation. How can
I do a datestamp that will not change?

Thank you.

http://www.mcgimpsey.com/excel/timestamp.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Alex Mackenzie" wrote in message
...
We use a spreadsheet to log incoming inspections of products. I want to
monitor how long it takes between receipt and inspection; receipt is
logged
into a different spreadsheet with date and time. I want to have the
inspection worksheet automatically date stamp when an inspection is
completed; I want to do this using IF(ISBLANK(xx),"",NOW()). The problem
is
that everytime the spreadsheet is opened the time is recalculated. I can
turn off recalc, but at times it is necessary to do a recalculation. How
can
I do a datestamp that will not change?

Thank you.

hi, Alex !

We use a spreadsheet to log incoming inspections of products.
I want to monitor how long it takes between receipt and inspection
receipt is logged into a different spreadsheet with date and time.
I want to have the inspection worksheet automatically date stamp when an inspection is completed
I want to do this using IF(ISBLANK(xx),"",NOW()).
The problem is that everytime the spreadsheet is opened the time is recalculated.
I can turn off recalc, but at times it is necessary to do a recalculation.
How can I do a datestamp that will not change?

you could try defining a UDF (you could reserve de undo stack-levels) i.e.

Function DateStamp(myCell As Range) As Date
Volatile = False
DateStamp = Now
End Function

then you can use it in the form: =if(a1<"",datestamp(a1))

hth,
hector.

You would have thought that by now (2008) Microsoft would have incorporated
this feature into Excel - I can remember back in the early '90s using a suite
of programs (word processor, database & spreadsheet) called SMART and in
their spreadsheet was a function NOCHANGE (this did not cause a circular
reference) so you could enter a formula, say in A1 =
IF(A10,NOCHANGE,TODAY()). This could of course be incorporated into other
formulae which enabled permanent datestamping without updating on
recalculation.
Wouldn't this be useful in Excel?????

"Héctor Miguel" wrote:

hi, Alex !

We use a spreadsheet to log incoming inspections of products.
I want to monitor how long it takes between receipt and inspection
receipt is logged into a different spreadsheet with date and time.
I want to have the inspection worksheet automatically date stamp when an inspection is completed
I want to do this using IF(ISBLANK(xx),"",NOW()).
The problem is that everytime the spreadsheet is opened the time is recalculated.
I can turn off recalc, but at times it is necessary to do a recalculation.
How can I do a datestamp that will not change?

you could try defining a UDF (you could reserve de undo stack-levels) i.e.

Function DateStamp(myCell As Range) As Date
Volatile = False
DateStamp = Now
End Function

then you can use it in the form: =if(a1<"",datestamp(a1))

hth,
hector.

hi, Ron !

You would have thought that by now (2008) Microsoft would have incorporated this feature into Excel
I can remember back in the early '90s using a suite of programs (word processor, database & spreadsheet) called SMART
and in their spreadsheet was a function NOCHANGE (this did not cause a circular reference) so you could enter a formula
say in A1 = IF(A10,NOCHANGE,TODAY()). This could of course be incorporated into other formulae
which enabled permanent datestamping without updating on recalculation.
Wouldn't this be useful in Excel?????

I'm sorry, I missed to state this UDF can not be used with reference to "itself" (calling cell)
accorging to OP who said: "... receipt is logged into a different spreadsheet ..."
and the exposed formula: " IF(ISBLANK(xx),"",NOW()) " (I don't know what the "xx" part refers to)

regards,
hector.

__ previous __
We use a spreadsheet to log incoming inspections of products.
I want to monitor how long it takes between receipt and inspection
receipt is logged into a different spreadsheet with date and time.
I want to have the inspection worksheet automatically date stamp when an inspection is completed
I want to do this using IF(ISBLANK(xx),"",NOW()).
The problem is that everytime the spreadsheet is opened the time is recalculated.
I can turn off recalc, but at times it is necessary to do a recalculation.
How can I do a datestamp that will not change?

you could try defining a UDF (you could reserve de undo stack-levels) i.e.

Function DateStamp(myCell As Range) As Date
Volatile = False
DateStamp = Now
End Function

then you can use it in the form: =if(a1<"",datestamp(a1))