Solved Adding date into cell when data is added to another cell

March 3, 2013 at 23:59:07
Specs: Windows 7

I've read through some other very similar posts and tried to make other solutions work to fit my problem however I've had no joy and it has been years since I really delved into formulae on excel.

I work in a pool hall and am trying to create a spreadsheet to account for table usage.

-What I am looking for is the current time (24h format) to be entered into column C when the user enters what table the customer has selected in column B (integer).

-If possible I would also like to enter the finish time into another column (D) when a checkbox is ticked.

This would allow the staff to work out rates without having to input times in a 24h format which I assume would confuse them plenty.

The second aspect to the problem is not as important as I am sure I could patch something together to enter the time if I can figure out the first part of the problem.

I am using excel 2002 (part of the office xp package)

Any help would be awesome,



See More: Adding date into cell when data is added to another cell

Report •

March 4, 2013 at 08:48:17
✔ Best Answer
Have you considered using a commercially available "pool hall management" software package? I did a quick Google search and found a few products, one for under $400. These packages have features like waiting lists, table usage data, food order tracking, and even the ability to control the lights over the tables.

As far as Excel goes, you will need a macro to add the time based on the input of a table number. Excel has a feature known as Event Macros which run when a specific event occurs.

For the Start Time, you can use the Worksheet_Change event which will run whenever a change is made to the sheet. The first thing it will do is check to see in which Column the change was made and if it matches the specified conditions (1 cell changed in Column B) then it will proceed with the rest of the instructions - e.g. put the time in Column C.

For the Finish Time, you can use the Worksheet_BeforeDoubleClick event so that the user simply has to double click a cell in Column D to insert the time.

Right click the sheet tab for the sheet you want this to happen in and choose View Code.

Paste the following code into the pane that opens. When a value is entered in a cell in Column B, the corresponding cell in Column C will display the current time.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 2 And Target.Cells.Count = 1 Then
  Range("C" & Target.Row) = Time
 End If
End Sub

For the Finish Time, paste this code in the same pane. When the user double clicks a cell in Column D, the current time will be entered.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Target.Column = 4 Then Target = Time
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question