How I can put static date by using any formula in excel. For ex. In Sheet 1 i
have entered payment is paid. I want to record on sheet 2 the date and time
automatical when it is paid.I used Now() function,but it changes whenever i
open my sheet. I want to use it like a Stamp.thanks
I don't Want to PUT MANUALLY everytime, i just want date and time there like
a stamp
I TRIED CTL+; and so on
thanks

Here is some VBA code that will write the current date like a timestamp in a
cell that you choose. Of course you will have to set the sheetname and cell

Sub TimeStamp()
Sheets("YourSheetName").Range("A1").Value = Now()
End Sub

To get it to work you need to put it in a normal module instructions are he
http://www.contextures.com/xlvba01.html

To run it you can put a button in your sheet very easily.
Edit Toolbars Forms

Click on the one called button and drag a button in your worksheet.
When you have done this, link it to the macro called TimeStamp that you have
set up.

Now you just need to bang on that button every time you want to update the
date.
The button can be in either sheet, it doesn't matter.

--
Allllen


"Jesse" wrote:

How I can put static date by using any formula in excel. For ex. In Sheet 1 i
have entered payment is paid. I want to record on sheet 2 the date and time
automatical when it is paid.I used Now() function,but it changes whenever i
open my sheet. I want to use it like a Stamp.thanks
I don't Want to PUT MANUALLY everytime, i just want date and time there like
a stamp
I TRIED CTL+; and so on
thanks

Code you need (change the Sheet2 and the cell if need be)

Sub TimeStamp()
Sheets("Sheet2").Range("A1").Value = Now()
End Sub

Put it in a regular module, instructions here
http://www.contextures.com/xlvba01.html

To run it, set up a button from the forms toolbar.
Edit Toolbars Forms
Choose the one called button
Drag it in your sheet.
Link it to the TimeStamp macro.

Hit the button every time you want to update that timestamp.

--
Allllen


"Jesse" wrote:

How I can put static date by using any formula in excel. For ex. In Sheet 1 i
have entered payment is paid. I want to record on sheet 2 the date and time
automatical when it is paid.I used Now() function,but it changes whenever i
open my sheet. I want to use it like a Stamp.thanks
I don't Want to PUT MANUALLY everytime, i just want date and time there like
a stamp
I TRIED CTL+; and so on
thanks

You could also take what Allllen has offered and put it into the worksheet's
code for the sheet where you enter the payment to be done automatically when
you make the entry there. The page at contextures.come that he links to has
a link to show how to place the code in a worksheet's module also.

Assuming that you enter your payment in cell B1 on sheet1 and want to record
the date in cell G1 on sheet2

This would go into the code associated with Sheet1 - the IsEmpty() test in
it will let the date get set when you first make an entry but not change the
date if you realize you had a typo in the payment amount (Sheet1!B1) later
and changed it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Target.Address, Range("B1"))
If iSect Is Nothing Then
Exit Sub
End If
If IsEmpty(Worksheets("Sheet2").Range("G1")) Then
Worksheets("Sheet2").Range("G1") = Now()
End If
End Sub


"Allllen" wrote:

Here is some VBA code that will write the current date like a timestamp in a
cell that you choose. Of course you will have to set the sheetname and cell

Sub TimeStamp()
Sheets("YourSheetName").Range("A1").Value = Now()
End Sub

To get it to work you need to put it in a normal module instructions are he
http://www.contextures.com/xlvba01.html

To run it you can put a button in your sheet very easily.
Edit Toolbars Forms

Click on the one called button and drag a button in your worksheet.
When you have done this, link it to the macro called TimeStamp that you have
set up.

Now you just need to bang on that button every time you want to update the
date.
The button can be in either sheet, it doesn't matter.

--
Allllen


"Jesse" wrote:

How I can put static date by using any formula in excel. For ex. In Sheet 1 i
have entered payment is paid. I want to record on sheet 2 the date and time
automatical when it is paid.I used Now() function,but it changes whenever i
open my sheet. I want to use it like a Stamp.thanks
I don't Want to PUT MANUALLY everytime, i just want date and time there like
a stamp
I TRIED CTL+; and so on
thanks

CORRECTION: I shouldn't be allowed to type blindly this early in the moring.
The Set iSect statement should look like this:

Set iSect = Application.Intersect(Range(Target.Address), Range("B1"))



"JLatham" wrote:

You could also take what Allllen has offered and put it into the worksheet's
code for the sheet where you enter the payment to be done automatically when
you make the entry there. The page at contextures.come that he links to has
a link to show how to place the code in a worksheet's module also.

Assuming that you enter your payment in cell B1 on sheet1 and want to record
the date in cell G1 on sheet2

This would go into the code associated with Sheet1 - the IsEmpty() test in
it will let the date get set when you first make an entry but not change the
date if you realize you had a typo in the payment amount (Sheet1!B1) later
and changed it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Target.Address, Range("B1"))
If iSect Is Nothing Then
Exit Sub
End If
If IsEmpty(Worksheets("Sheet2").Range("G1")) Then
Worksheets("Sheet2").Range("G1") = Now()
End If
End Sub


"Allllen" wrote:

Here is some VBA code that will write the current date like a timestamp in a
cell that you choose. Of course you will have to set the sheetname and cell

Sub TimeStamp()
Sheets("YourSheetName").Range("A1").Value = Now()
End Sub

To get it to work you need to put it in a normal module instructions are he
http://www.contextures.com/xlvba01.html

To run it you can put a button in your sheet very easily.
Edit Toolbars Forms

Click on the one called button and drag a button in your worksheet.
When you have done this, link it to the macro called TimeStamp that you have
set up.

Now you just need to bang on that button every time you want to update the
date.
The button can be in either sheet, it doesn't matter.

--
Allllen


"Jesse" wrote:

How I can put static date by using any formula in excel. For ex. In Sheet 1 i
have entered payment is paid. I want to record on sheet 2 the date and time
automatical when it is paid.I used Now() function,but it changes whenever i
open my sheet. I want to use it like a Stamp.thanks
I don't Want to PUT MANUALLY everytime, i just want date and time there like
a stamp
I TRIED CTL+; and so on
thanks