Capturing Time in Excel cell

Is it possible to get a static time in cell B1 once data is selected from a drop down in cell A1 with the use of a formula ?
nicolas007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Hi Nicolas,

Unfortunately, you cannot create a static time with a formula because it will update when the  worksheet calcualtes and re-evaluate the formula.

You can however, do this with VBA code.  If you paste the code below in the worksheet code window, it will do exactly what you are after.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'if the cell is A1 then...
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        With Range("B1")
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
End Sub

Open in new window

0
unknown_routineCommented:
Yes it is possible Just add a time function to a module in VBA
0
nicolas007Author Commented:
It did work but when I give it like this the second If statement is not working is not executing

Private Sub Worksheet_Change(ByVal Target As Range)
    'if the cell is A1 then...
    If Not Intersect(Target, Range("D1")) Is Nothing Then
        With Range("G1")
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
   
   
     If Not Intersect(Target, Range("G1")) Is Nothing Then
        With Range("J1")
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
   
End Sub
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

FamousMortimerCommented:
Strange... it worked on my test sheet.  Delete all of the code and paste this into the sheet.  Let me know.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    'if the cell is A1 then...
    If Not Intersect(Target, Range("D1")) Is Nothing Then
        With Range("G1")
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    ElseIf Not Intersect(Target, Range("G1")) Is Nothing Then
        With Range("J1")
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
End Sub

Open in new window

0
nicolas007Author Commented:
Please find the attached file. Once the user selects the Task Type I want Start Time to be captured and once he selects the status End Time should appear. It would be really helpful if you can help me with the tracker I requested in another question.
TM-Template.xlsm
0
FamousMortimerCommented:
After seeing it, it makes much more sense to me.  Check out the attached file and see if that works for you.

I set it up so that when a value is chosen in any cell from D2 to D500 or G2 to G500 it will enter the start time or end time respectively.
Private Sub Worksheet_Change(ByVal Target As Range)
    'if the cell is A1 then...
    If Not Intersect(Target, Range("D2:D500")) Is Nothing And Target.Cells(1).Value <> vbNullString Then
        With Range("H" & Target.Row)
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    ElseIf Not Intersect(Target, Range("G2:G500")) Is Nothing And Target.Cells(1).Value <> vbNullString Then
        With Range("J" & Target.Row)
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
End Sub

Open in new window

<<Code snippet added by byundt, Microsoft Excel Topic Advisor, so comment doesn't include a "blind" link. This is part of a new push by Experts-Exchange to improve the quality of posts and make them rank higher in Google searches>>
TM-Template.xlsm
0
nicolas007Author Commented:
It does not restrict the user give a endtime when the starttime is blank. This will lead to more confusion in our end. Also, is it possible to get the US Date once the user reaches the Date Column automatically ?
0
FamousMortimerCommented:
Give this one a try.

It will only enter the end time if the start time is entered.
When the user selects the cell in column B, it will automatically enter the date in MM/dd/yyyy format in that cell if the cell is empty

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    'if the cell is A1 then...
    If Not Intersect(Target, Range("D2:D500")) Is Nothing And Target.Cells(1).Value <> vbNullString Then
        With Range("H" & Target.Row)
            'change the value and numberformatting of B1
            'if you want the time to show as 6:30:00 PM change the format to "hh:mm:ss AM/PM"
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    ElseIf Not Intersect(Target, Range("G2:G500")) Is Nothing And Target.Cells(1).Value <> vbNullString And Cells(Target.Row, 8).Value <> vbNullString Then
        With Range("J" & Target.Row)
            .Value = Format(Now(), "hh:mm:ss AM/PM")
            .NumberFormat = "hh:mm:ss AM/PM"
        End With
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:B500")) Is Nothing And Target.Value = vbNullString Then
        Target.Value = Format(Date, "mm/dd/yyyy")
    End If
End Sub

Open in new window


TM-Template.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FamousMortimerCommented:
Wow... Grade B? On 50 points?  Considering that I wrote all of the code and uploaded a working workbook for you, I am insulted.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.