Freeze NOW formula

C

cameron

I have entered a conditional formula, that is if an adjacent cell contains a
number then the NOW function will enter the current date and time into
another cell. If I copy this formula in subsequent cells and enter a number
into the conditional cell excel recalcs both cells, however I want the first
cell to calculate once then freeze. The reason for this is i want to generate
unique document numbers from exact times and dates.
 
M

Myrna Larson

You can use shortcut keys to enter the current date and time: CTRL+; for the
date and CTRL+SHIFT+: for the time.
 
E

Earl Kiosterud

Cameron,

I'm not sure I should be suggesting this, but you might want to try it. You
can latch the time in the cell with a deliberate circular reference (a
self-referential formula). You put it in A2, and when you put anything into
B2, the date-time gets latched into the cell, and stays put as long as
there's something in B2. You can copy it, and the other cell, and it will
keep its date-time value.

=IF(AND(A2="",B2<>""),NOW(),IF(AND(A2<>"",B2<>""),A2,""))

This is in A2. You must turn on "Iteration" in Tools - Options -
Calculation, and set max iterations to 1.

I don't consider this solid design. An event macro that generates your ID
numbers based on the date-time would probably be a better solution. But
it's fun!
 
C

cameron

Thanks Earl will give it a try. Not real good with macros and visual basic
but i agree it would be a better solution. Will study up and see if i can
work it out.

Cameron
 
S

sunroyal

What are the cells? I have a productivity book that runs a similar cod
to calculate call times in a call center. It places an 'x' in a cell
and I use a fuction like this =if(c2="x",now(),""))
 
C

cameron

What i am trying to achieve is as follows:

Column A is the date column
Col B is a job code column
and Col C will be the unique document number.
This (and some other information in the spreadsheet) will create a
correspondence register.

The way i want to set it up is as follows:

The project manager wants to write some correspondence for the job he is
doing.
He inputs the job code into a B cell and from this input the date and time
is generated in the A cell and in the C cell a number (in the excel date
format) is generated which, along with the job number will be a unique
identification number for that document. From here I want to link our fax and
formal correspondence templates to this register to copy the unique
identifier over. If I can get it to work it will create a very good tool for
tracability and at the same time making it all but idiot proof (removing the
need for a formal procedure for documentation numbering and tracability and
the associated training)
 
D

David McRitchie

Hi Cameron,

You should be using an Event macro
http://www.mvps.org/dmcritchie/excel/event.htm#autotime
You can use NOW for date + time, or you can use DATE or you can use TIME.

The macro at the above location has been modified for your
description as best I can guess, but it should be obvious how
you can change it. The target is the cell that got changed
by your manual entry. .

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module.
If Target.Column <> 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, -1)) Then
Target.Offset(0, -1) = Date + TimeSerial(Hour(Time), Minute(Time), 0)
Target.Offset(0, -1).NumberFormat = "yyyy-mm-dd hh:mm"
Target.Offset(0, 1).Value = Target.Value & Format(Target.Offset(0, -1), _
"yyyymmddhhmm")
End If
End Sub
 
Top