I use an Excel invoice to bill clients.
Currently I have an Invoice Date field which is populated by the
=TODAY() function. That's good. However, if I open the spreadsheet down
the road and accidentally recalculate, the Invoice Date field is
updated with the current date. That's not so good.

Is there a simple macro I can use that Inserts the date when the
document is created and then terminates? Something, I guess, that calls
the TODAY() function and then pops the resulting date value into the
appropriate cell.

I know I can just use CTRL+; to insert the date manually but I'm
shooting for *fewer* things that I can potentially forget when
invoicing.

Thanks,

KZ

One way:

Put this in your ThisWorkbook code module. Change "A1" to whatever cell
you want the date to be entered in, and modify the date format to suit:

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

Save your template with A1 cleared.


In article . com,
"Ken Zenachon" wrote:

I use an Excel invoice to bill clients.
Currently I have an Invoice Date field which is populated by the
=TODAY() function. That's good. However, if I open the spreadsheet down
the road and accidentally recalculate, the Invoice Date field is
updated with the current date. That's not so good.

Is there a simple macro I can use that Inserts the date when the
document is created and then terminates? Something, I guess, that calls
the TODAY() function and then pops the resulting date value into the
appropriate cell.

I know I can just use CTRL+; to insert the date manually but I'm
shooting for *fewer* things that I can potentially forget when
invoicing.

Thanks,

KZ

Thanks, I'll try that and get back to you.

KZ

JE,

i tried this code in excel 03 on xp and it does not work.... well it must
coming from you but i am not seeing it happen...lol...i think it is me.

I have a workbook that has a customer info sheet which creates a 1-of-a-kind
number based on the customers first and last names and the date customer
calls for service. Right now i must manualy enter the date so the customer
number can be generated. can you help....

this is the basic look of the sheet

My Co. Name DATE
__________________________________________
last name Customer No.
first name
__________________________________________
Cust. Address
Street
City St. Zip

DATE = Cell I1:J1 this is the cell which needs the auto date (date format
should be mmddyy with no puctuation)... this date can not however be volitile
or the customer number will change and that would be bad...lol

another thing i am having bad luck with is - i need to "auto save as" the
workbook b4 closeing .... i would like to have the workbook saved using the
generated customer number as the file name

the CUSTOMER NO. cell = I10:J10

i honestly do not know if the code windows are ready for me cause i never
used them b4...just found out that they were there yesterday...lol

anthing you can do to help would be great!

"JE McGimpsey" wrote:

One way:

Put this in your ThisWorkbook code module. Change "A1" to whatever cell
you want the date to be entered in, and modify the date format to suit:

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

Save your template with A1 cleared.


In article . com,
"Ken Zenachon" wrote:

I use an Excel invoice to bill clients.
Currently I have an Invoice Date field which is populated by the
=TODAY() function. That's good. However, if I open the spreadsheet down
the road and accidentally recalculate, the Invoice Date field is
updated with the current date. That's not so good.

Is there a simple macro I can use that Inserts the date when the
document is created and then terminates? Something, I guess, that calls
the TODAY() function and then pops the resulting date value into the
appropriate cell.

I know I can just use CTRL+; to insert the date manually but I'm
shooting for *fewer* things that I can potentially forget when
invoicing.

Thanks,

KZ

Well, I did as you said but it doesn't seem to work.
Am I supposed to save the worksheet as an XLT template or should just
opening the XLS file with the code in the ThisWorkbook module do the
trick?

KZ

Since you don't say what "doesn't seem to work" means, it's hard to
tell. Is nothing happening? the wrong value being entered?

The code should be entered in the ThisWorkbook module of the XLT. The
XLT should have the target cell blank when saved.

In article . com,
"Ken Zenachon" wrote:

Well, I did as you said but it doesn't seem to work.
Am I supposed to save the worksheet as an XLT template or should just
opening the XLS file with the code in the ThisWorkbook module do the
trick?

Insert the current date and time in a cell

Select a cell and press CTRL+;


--
rsenn
------------------------------------------------------------------------
rsenn's Profile: http://www.excelforum.com/member.php...o&userid=29050
View this thread: http://www.excelforum.com/showthread...hreadid=495570

Sorry, my answer was a bit vague...
When I first enteredthe formula into my XLS (not XLT) nothing happened
immediately, and nothing happened when I closed and reopened the
document. I'm going to save it as a template and get back to you.
KZ

Hey, it works!
:)

Thanks very much.

KZ