freezing todays date in a function

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,
I am using Excel 2000 for invoicing. I would like to freeze the
current date so that when I reopen the workbook it shows the date the
invoice was created.

I can use =now() in the cell and then a macro to freeze the date.
Sub FreezeDate()
'
' FreezeDate Macro
' Macro recorded 13/05/99 by David Carpenter
'

'
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

I would like to use a function to freeze the date rather than using a
macro.
I can do one where I put =created() in a cell and then
Function Created()
Created = ActiveWorkbook.BuiltinDocumentProperties.Item(11)

End Function

But this gives me the date when the original file was created not
freezing today's date. Can anyone suggest how I can change the
Function module?

Regards
David
dapeca@hotmaildotCom
 
Hi Dave!

This can't be done by a function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Dave,

You could use a simple UDF

Function FreezeDate()
FreezeDate = Date
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob!

As long as no-one uses Ctrl + Alt + F9 or Ctrl + Shift + Alt + F9

This is best illustrated using Now instead of Date. Even functions
without links to other cells are recalculated with those forms of
re-calculation.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Instead of using a macro to freeze a worksheet function value, you might
consider using a workbook_Open() macro in a template to insert the date
automatically.

For instance, you could put this in your ThisWorkbook code module:

Private Sub Workbook_Open()
With Sheet1.Range("A1")
If IsEmpty(.Value) Then
.NumberFormat = "dd mmm yyyy"
.Value = Date
End If
End With
End Sub

Save the workbook as a template with Sheet1!A1 blank. Whenever you then
use the template to create a new invoice, the current date will be
entered.

You can still edit the date if needed. As long as Sheet1!A1 has a value,
it won't be overwritten by the macro.
 
This is a big problem that should go on the wishlist.

Anyone who is in the grip of some regulatory authority will know why: it's
very hard to prove that any sort of program is valid, but you can often get
away with using functions.

Regards
 
JE McGimpsey said:
Instead of using a macro to freeze a worksheet function value, you might
consider using a workbook_Open() macro in a template to insert the date
automatically.
Click to expand...
....

Same general idea I had, but I'd do it differently. I'd have the Open event
add the workbook-level name __NOW__ with the date/time value when the
workbook was first opened by a user (rather than the developer, who would
need to leave __NOW__ undefined or initialized to #N/A). Then the name
__NOW__ could be used anywhere in any formula in the workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub
 
Harlan Grove said:
Same general idea I had, but I'd do it differently. I'd have the Open event
add the workbook-level name __NOW__ with the date/time value when the
workbook was first opened by a user (rather than the developer, who would
need to leave __NOW__ undefined or initialized to #N/A). Then the name
__NOW__ could be used anywhere in any formula in the workbook.
Click to expand...

I like your idea much better!
 
Back
Top