CODEWITHSUNDEEP
excelvba
user6167313
user6167313

Reputation:

Automatically Fetch Current Time in Excel

I am trying to build out a task tracker for my job that aggregates the amount of time I spend on various assignments.

Example rows of task tracker

Currently I am entering the start and finish times for each task manually with Ctrl + ;, Space, Ctrl + Shift +;. What I would like to do, if possible, is to have the start time column populate with the current time at whatever moment I set the drop down option to "Work in Progress," and then the end time column to do the same for whatever moment I change it to "Closed."

Drop Down Menu

Any ideas?

Upvotes: 0

Views: 93

Answers (3)

ashleedawg
ashleedawg

Reputation: 21619

If the Drop Down is a Form Control (as opposed to an ActiveX control) then you could right-click it and choose Assign Macro.

It should default to something like DropDown1_Change, and you can click New.

Then paste in code "something like" this:

Sub DropDown1_Change()
    If Range("A4") = 1 Then Range("A1") = Now()
End Sub

The first & last lines should already be populated for you.

You'd need to adjust the code to your needs. In this example:

  • The Drop Down is named Drop Down 1.
  • Cell A4 is the Cell Link for my Drop Down.
  • Cell A1 is the cell I want the date in.
  • I want to run the code when item # 1 is selected from the Drop Down's Input Range.

If your control happens to be an ActiveX DropDown/ComboBox, the steps are similar:

  • Right click the control and choose View Code.
  • You'll be taken immediately to the VBA Editor.
  • Paste the code.

Upvotes: 1

Terminader
Terminader

Reputation: 70

Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FirstRow, StatusColumn, CurrentRow, CurrentColumn As Double
    FirstRow = 5        'Define here the first row number of data
    StatusColumn = 5    'Define here the column number of Status
    CurrentRow = Target.Row
    CurrentColumn = Target.Column
    If CurrentColumn = StatusColumn And CurrentRow >= FirstRow Then
        Select Case CStr(Target.Value)
            Case "Work In Progress":
                Cells(CurrentRow, CurrentColumn + 2) = Format(Now, "MM.dd.yy hh:mm")
                Cells(CurrentRow, CurrentColumn + 3) = ""
            Case "Closed":
                Cells(CurrentRow, CurrentColumn + 3) = Format(Now, "MM.dd.yy hh:mm")
            Case Else:
                'Some code here...
        End Select
    End If
End Sub

Upvotes: 0

R. Hoek
R. Hoek

Reputation: 1106

The only way to do this, is using macro's.

In VBA (Excel -> Alt+F11) you can handle create events to handle things like cell changes.

The double click on the sheet you're entering your task tracker data and add the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Now you can use the Target parameter to detect the cell which has been changed and then modify some other cell based on the changes cell.

So let's assume you have the dropdown in colum '4' and the begin and end time in column '5' and '6'. Then using the code below will update the begin and endtime field based on what state the taks has:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        If Target.Value = "Work in Progress" Then
            Me.Cells(Target.Row, 5).Value = Time
        ElseIf Target.Value = "Closed" Then
            Me.Cells(Target.Row, 6).Value = Time
        End If
    End If
End Sub

NOTE You need to create an Excel workbook with macro's which require you to enable them - by default Excel disables macro's - so take note on enabling them when asked.

Upvotes: 0

Related Questions