• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IS it possible in excel

J

Jagdev Singh

Active Member
Hi Experts

I want to set the date format to DD/MM/YYY hh:mm:ss on a column. What I am looking for is that the user adds a date in a particular column and the date should display in DD/MM/YYY hh:mm:ss format. It should capture the current time with the date. Is it possible

Ex - when someone try to add a date 13/05/2015 in a cell of a particular date column. The respective date should get convert to 13/05/2015 14:25:50

Regards,
JD
 
Hi Sudipta

Thanks for looking into it. If I am not wrong the now() funtion is dynamic, it keeps on changing the date everytime a worksheet is reopen. If I use now() then to make it static I have to follow the below steps.

CTRL+; (SEMICOLON) with CTRL+SHIFT+: (COLON)

I am looking for something which accept the date and display it with the the current time in the cell.

Regards,
JD
 
Do OP want current date to be followed by time or any date entered in a cell be followed by time? I think he just wants to knew when a certain date is entered a cell.
 
Hi
You mentioned that the user is going the enter the date , in that case the date can be anything and not just the current date, So in this case you should rely on the user itself to input the hours and minutes also IMHO..
 
It's a workaround..

=A1+(NOW()-TODAY())

where A1 is the user date.

You can also manipulate the same by vba.
 
Hi Deepak

I find the below code which helped me to reach the goal.

Code: 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range
   
    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("A:A"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, 1)
                    .Value = Now
                    .NumberFormat = "hh:mm:ss"
                End With
            Else
                rCell.Offset(0, 1).Clear
            End If
        Next
    End If
ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

Regards,
JD
 
You must log in or register to reply here.
Back
Top