| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to Stop =TODAY() date function changing byitself
Hi,
I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
=TEXT(TODAY(),"ddmmyy")&"-xx"
"Kevin" wrote: Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Thanks ! I tried following formula but it still changes the date to current
date as set on your computer. Which doesn't solve the purpose because idea is to keep the quote number fixed once file is saved. I have also tried to use "paste special" in another cell as you mentioned. It works fine but how can I automate that process so that i don't need to do manual copy paste each time I generate a quote. Thanks Kevin "Teethless mama" wrote: =TEXT(TODAY(),"ddmmyy")&"-xx" "Kevin" wrote: Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Copy paste special Value
"Kevin" wrote: Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Instead of =TODAY() you could use Control semicolon to insert the date.
-- David Biddulph "Kevin" wrote in message ... Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Hi David,
That's what I'm doing at the moment but I like to automate the process so that whenever I open a new document date is already there. Thanks Kevin "David Biddulph" wrote: Instead of =TODAY() you could use Control semicolon to insert the date. -- David Biddulph "Kevin" wrote in message ... Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Kevin
By "open a new document" do you mean based on a Template(*.xlt)? If so, you can put code in the Template Thisworkbook module that sticks a static date into a cell when a new document is created from the Template. Private Sub Workbook_Open() With ActiveSheet.Range("A1") If .Value = "" Then .Value = Format(Now, "ddmmmyy") End If End With End Sub When you subsequently open the saved document if the date is in A1 it will not change. Gord Dibben MS Excel MVP On Mon, 1 Jan 2007 01:54:00 -0800, Kevin wrote: Hi David, That's what I'm doing at the moment but I like to automate the process so that whenever I open a new document date is already there. Thanks Kevin "David Biddulph" wrote: Instead of =TODAY() you could use Control semicolon to insert the date. -- David Biddulph "Kevin" wrote in message ... Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
Thanks Gord,
That's very close what I wanted. Thanks for your help. Rgds, Kevin "Gord Dibben" wrote: Kevin By "open a new document" do you mean based on a Template(*.xlt)? If so, you can put code in the Template Thisworkbook module that sticks a static date into a cell when a new document is created from the Template. Private Sub Workbook_Open() With ActiveSheet.Range("A1") If .Value = "" Then .Value = Format(Now, "ddmmmyy") End If End With End Sub When you subsequently open the saved document if the date is in A1 it will not change. Gord Dibben MS Excel MVP On Mon, 1 Jan 2007 01:54:00 -0800, Kevin wrote: Hi David, That's what I'm doing at the moment but I like to automate the process so that whenever I open a new document date is already there. Thanks Kevin "David Biddulph" wrote: Instead of =TODAY() you could use Control semicolon to insert the date. -- David Biddulph "Kevin" wrote in message ... Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to Stop =TODAY() date function changing byitself
You can't do it using a function, use procedure instead (you can define a
shortcut to call it). An example: Public Sub GetUnique() 'Procedure inserts a new unique ID (ddmmyy-####) into active cell, when it is in specific column ' IDCol defines the column where new ID's are inserted Dim IDCol As Integer ' HeaderRows determines rows at top, where none ID's are inserted Dim HeaderRows As Integer IDCol = 1 HeaderRows = 1 If ActiveCell.Column = IDCol And ActiveCell.Row HeaderRows Then Dim MaxID As Integer Dim i As Integer Dim LastRow As Integer MaxID = 0 LastRow = ActiveSheet.UsedRange.Rows.Count If LastRow HeaderRows Then For i = HeaderRows + 1 To LastRow If ActiveSheet.Cells(i, IDCol).Value = "" Then ElseIf IsNumeric(Mid(ActiveSheet.Cells(i, IDCol).Value, 8, 33)) Then MaxID = IIf(CInt(Mid(ActiveSheet.Cells(i, IDCol).Value, 8, 33)) MaxID, CInt(Mid(ActiveSheet.Cells(i, IDCol).Value, 8, 33)), MaxID) End If Next i End If ActiveCell.Value = Format(MaxID, "ddmmyy") & "-" & Format(MaxID + 1, "0000") End If End Sub "Kevin" wrote in message ... Hi, I'm trying to create an Excel form to generate a quotation for my customers. I like to have quotation number extracted from today's date e.g. if I generate quote on 30Dec06 then prefix of quote number could be 301206-xx I have tried to use =TODAY() date function but everytime I open excel file it changes to today's date. Is there any way I can extract the contents of today's date and paste them in a cell and once pasted should never change. Thanks in advance. Regards, Kevin kksmith67 at yahoo.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Last Saved Date Formula / Function | Excel Discussion (Misc queries) | |||
| Changing worksheet cells from within a function | Setting up and Configuration of Excel | |||
| When I open my past invoice it keeps changing date to todays date | Excel Worksheet Functions | |||
| NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
| How to stop text changing to date | Excel Discussion (Misc queries) | |||
Hybrid Mode
