Date and Time Stamp in Excel 1

[RachieD]

Hi

I used the following FAQ707-1657 to set up a date stamp in Excel when data is entered, this worked fine.

Does anyone know how I can record the time too? At present the date is correct but the time shows as 00:00.

I amended the above FAQ with the .NumberFormat below, but it made no difference:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    For Each Cell In Target
        With Cell
            If .Column = Range("G:G").Column Then
                Cells(.Row, "F").Value = Int(Now)
                .NumberFormat = "MM/dd/yy hh:mm:ss"
                End If
                End With
                Next Cell
            
End Sub

I don't mind if the time is recorded in the same cell as the date or a different one, but I would like to be able to see both.

Thanks!!
RachieD

 

[TonyJollans]

Hi RachieD,

Now gives you the Date and Time. In this code the time has been chopped off by use of the Int function. To keep the Time part, change

[purple]Cells(.Row, "F").Value = Int(Now)[/purple]

to

[blue]Cells(.Row, "F").Value = Now[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[shetlandbob]

Performing Int on the now removes the time stamp from the date

Robert Cumming

http://www.consultantengineer.co.uk

 

[shetlandbob]

ah you beat me to it! (and a more descriptive answer!)



Robert Cumming

http://www.consultantengineer.co.uk

 

[TonyJollans]

Hi Bob,

I do try to give descriptive answers which usually means someone gets in before me with a shorter one - but not this time

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[RachieD]

Thanks Tony - it worked!! I appreciate your help.

RachieD

 

[shetlandbob]

Tony,

Ah well, Ill just have to type faster next time!!

Cheers

Robert Cumming

http://www.consultantengineer.co.uk