I want a function for typing "R" in one cell leads to a clock starting in another cell . "P" stops the time

[SpaulS]

Hi all, Im novice in excel and building a planning tool.
I would like to create a function that when typing a word in one cell leads to starting a time count in another cell.

Example.
I get 100+ orders from a clients every week. i want a simple chart to get a overlook of the orders and how much time since they were registred, then click a box or write something to make the time stop.


Someting like this:
I write write "R" for recieved in a cell A1. "R" should be linked to starting a clock in C1 that displays the time since i recieved the order. When the order is processed i want to write "P" for processed in A1 that stops the time in C1 from running. The time is preferrable in hours or hours and days.


Is this possible for a novice excel user to achieve?

 

[mumps]

Have a look at this link: https://www.excelforum.com/excel-pr...in-excel-with-start-pause-and-end-button.html
particularly the file attached in Post #13 .

 

[SpaulS]

Have a look at this link: https://www.excelforum.com/excel-pr...in-excel-with-start-pause-and-end-button.html
particularly the file attached in Post #13 .

-
Thats close! i was hoping to not have to use the button but use a word that i used for colour-coding my scheme.
like in the example that "R" could start the clock.(as it also colourcodes my chart)
Also in this timer it only shows you the time when you stop or paus it. i would need to see it in realtime.

 

[Perpa]

-
Thats close! i was hoping to not have to use the button but use a word that i used for colour-coding my scheme.
like in the example that "R" could start the clock.(as it also colourcodes my chart)
Also in this timer it only shows you the time when you stop or paus it. i would need to see it in realtime.

SpaulS,
Assuming all your R and P entries are in column A, not just A1, you can accomplish what you have asked with a helper
column B to show the elapsed time when you enter the 'P' using the 'Worksheet_Change' code below.
Columns B & C must be formatted as follows :
- Column B should be formatted 'Custom>[hh]:mm:ss'.
- Column C should be formatted 'Custom>[$-409]m/d/yy h:mm:ss AM/PM;@.
You may have to widen column C to accomodate that format otherwise you'll just see a string of '###########'.

Here is the way I set it up:

Sheet1

	A	B	C
1	R' or 'P'	Time Since Received	Received - Time
2
3	P	0:02:45	11/12/2017 11:12
4
5	P	0:04:00	11/12/2017 11:12
6
7	P	0:07:33	11/12/2017 11:12
8
9	P	0:00:13	11/12/2017 10:50
10
11	P	0:00:07	11/12/2017 10:52
12
13	R		11/12/2017 11:24

<tbody>
</tbody>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


To enter the code, right click on the Sheet tab at the bottom of the worksheet, this opens the VB Editor. In the selection slot labled 'General' click on the down arrow on the RH side and select 'Worksheet', then paste the code below right over the two lines that are shown as default. Then close the VB Window and save the workbook as maco enabled. Make a copy to test the macro so you do not lose anything important in your original file. Depending on the version of Excel you are running you may have to enable macros to run the code when you reopen the file.
Perpa

Private Sub Worksheet_Change(ByVal Target As Range)
'Code enters current Time in column C when 'R'  is entered in column A
'Then calculates the difference in hh:mm:ss in column B when 'P' is been entered in column A

    If Target.Count = 1 Then
        If Not Intersect(Target, Columns("A")) Is Nothing Then     'The TARGET Column is A
            If Target.Value = "R" Then
                Target.Offset(0, 2).Value = Now()
                Target.Offset(0, 1).Value = ""
            End If
        
            If Target.Value = "P" Then
                Target.Offset(0, 1) = Now() - Target.Offset(0, 2).Value
                'Target.Value = Now() - Target.Offset(0, 1).Value
                
            End If
        End If
    End If
End Sub