how to Stop =TODAY() date function changing byitself

[Guest]

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

 

[Guest]

=TEXT(TODAY(),"ddmmyy")&"-xx"

 

[Guest]

Copy > paste special > Value

 

[Guest]

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

 

[David Biddulph]

Instead of =TODAY() you could use Control semicolon to insert the date.

 

[Guest]

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

 

[Gord Dibben]

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

 

[Arvi Laanemets]

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