software

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,

KZOne way:

Put this in your ThisWorkbook code module. Change quot;A1quot; 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(quot;A1quot;)
If IsEmpty(.Value) Then
.NumberFormat = quot;dd mmmm yyyyquot;
.Value = Date
End If
End With
End Sub

Save your template with A1 cleared.In article . comgt;,
quot;Ken Zenachonquot; gt; wrote:

gt; I use an Excel invoice to bill clients.
gt; Currently I have an Invoice Date field which is populated by the
gt; =TODAY() function. That's good. However, if I open the spreadsheet down
gt; the road and accidentally recalculate, the Invoice Date field is
gt; updated with the current date. That's not so good.
gt;
gt; Is there a simple macro I can use that Inserts the date when the
gt; document is created and then terminates? Something, I guess, that calls
gt; the TODAY() function and then pops the resulting date value into the
gt; appropriate cell.
gt;
gt; I know I can just use CTRL ; to insert the date manually but I'm
gt; shooting for *fewer* things that I can potentially forget when
gt; invoicing.
gt;
gt; Thanks,
gt;
gt; KZ

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

KZJE,

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 quot;auto save asquot; 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!

quot;JE McGimpseyquot; wrote:

gt; One way:
gt;
gt; Put this in your ThisWorkbook code module. Change quot;A1quot; to whatever cell
gt; you want the date to be entered in, and modify the date format to suit:
gt;
gt; Private Sub Workbook_Open()
gt; With Sheets(1).Range(quot;A1quot;)
gt; If IsEmpty(.Value) Then
gt; .NumberFormat = quot;dd mmmm yyyyquot;
gt; .Value = Date
gt; End If
gt; End With
gt; End Sub
gt;
gt; Save your template with A1 cleared.
gt;
gt;
gt; In article . comgt;,
gt; quot;Ken Zenachonquot; gt; wrote:
gt;
gt; gt; I use an Excel invoice to bill clients.
gt; gt; Currently I have an Invoice Date field which is populated by the
gt; gt; =TODAY() function. That's good. However, if I open the spreadsheet down
gt; gt; the road and accidentally recalculate, the Invoice Date field is
gt; gt; updated with the current date. That's not so good.
gt; gt;
gt; gt; Is there a simple macro I can use that Inserts the date when the
gt; gt; document is created and then terminates? Something, I guess, that calls
gt; gt; the TODAY() function and then pops the resulting date value into the
gt; gt; appropriate cell.
gt; gt;
gt; gt; I know I can just use CTRL ; to insert the date manually but I'm
gt; gt; shooting for *fewer* things that I can potentially forget when
gt; gt; invoicing.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; KZ
gt;

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?

KZSince you don't say what quot;doesn't seem to workquot; 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 . comgt;,
quot;Ken Zenachonquot; gt; wrote:

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


Insert the current date and time in a cell

Select a cell and press CTRL ;--
rsenn
------------------------------------------------------------------------
rsenn's Profile: www.excelforum.com/member.php...oamp;userid=29050
View this thread: www.excelforum.com/showthread...hreadid=495570Sorry, 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.
KZHey, it works!Thanks very much.

KZ