Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
I want to create a sheet for use in a Contact Centre to record when people are being allowed off the phones to do other work. My objective is a three column sheet (with addition columns for text) where, if a person enters a start time value in column B and and end time value in column C, the system time (when the entry in B was made) appears in column A, but as a fixed and not as a volatile value. This is necessary so that we can see not only the time the person is recorded as having done the work, but the time we are informed about them starting! It may appear semantic but if we are told at 8pm that someone was working on a project from noon until 1300 then we would not allow that "exception" to go through and we need to know in real time. Can this be done? Thanks in advance and apologies if I am posting too much detail or in the wrong forum. Hillheader -- Hillheader ------------------------------------------------------------------------ Hillheader's Profile: http://www.excelforum.com/member.php...o&userid=30644 View this thread: http://www.excelforum.com/showthread...hreadid=503040 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
If you are looking to record actual times, I would suggest you just give them
a button to record the system time. So when they start work they click the button and it gets logged in column A. You could just track the times in one column and then match them up later but a better suggestion would be to have 2 buttons. One for start (gets logged in column A) and the other for end (gets logged in column B). If your afraid they will forge the time, don't give them the chance to. -Stanley "Hillheader" wrote: I want to create a sheet for use in a Contact Centre to record when people are being allowed off the phones to do other work. My objective is a three column sheet (with addition columns for text) where, if a person enters a start time value in column B and and end time value in column C, the system time (when the entry in B was made) appears in column A, but as a fixed and not as a volatile value. This is necessary so that we can see not only the time the person is recorded as having done the work, but the time we are informed about them starting! It may appear semantic but if we are told at 8pm that someone was working on a project from noon until 1300 then we would not allow that "exception" to go through and we need to know in real time. Can this be done? Thanks in advance and apologies if I am posting too much detail or in the wrong forum. Hillheader -- Hillheader ------------------------------------------------------------------------ Hillheader's Profile: http://www.excelforum.com/member.php...o&userid=30644 View this thread: http://www.excelforum.com/showthread...hreadid=503040 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
Stanley Thanks for taking the time to reply. We accept that there will be a bit of a time lag and that the record will not be created in true "real" time. We are trying to avoid the situation where the person comes off at noon and we do not get told until 8pm. Repeat offenders would receive robust feedback!!! Do you know if there is a way of recording the time of entry in an automatic, but non volatile way? Sorry for the earlier confusion. Thanks again -- Hillheader ------------------------------------------------------------------------ Hillheader's Profile: http://www.excelforum.com/member.php...o&userid=30644 View this thread: http://www.excelforum.com/showthread...hreadid=503040 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
Hill
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'when entering data in a cell in Col B Application.EnableEvents = False On Error GoTo enditall If Target.Cells.Column = 2 Then n = Target.Row If Excel.Range("B" & n).Value < "" Then Excel.Range("A" & n).Value = Now End If End If enditall: Application.EnableEvents = True End Sub This is sheet event code. Right-click on the sheet tab and "View Code". Copy/paste into that module. Macros must be enabled by the user when opening the workbook for this code to be available. Gord Dibben MS Excel MVP On Thu, 19 Jan 2006 13:31:30 -0600, Hillheader wrote: I want to create a sheet for use in a Contact Centre to record when people are being allowed off the phones to do other work. My objective is a three column sheet (with addition columns for text) where, if a person enters a start time value in column B and and end time value in column C, the system time (when the entry in B was made) appears in column A, but as a fixed and not as a volatile value. This is necessary so that we can see not only the time the person is recorded as having done the work, but the time we are informed about them starting! It may appear semantic but if we are told at 8pm that someone was working on a project from noon until 1300 then we would not allow that "exception" to go through and we need to know in real time. Can this be done? Thanks in advance and apologies if I am posting too much detail or in the wrong forum. Hillheader |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
Gord I do not pretend to understand this but I will give it a go!!! Many Many Many Thanks!!! -- Hillheader ------------------------------------------------------------------------ Hillheader's Profile: http://www.excelforum.com/member.php...o&userid=30644 View this thread: http://www.excelforum.com/showthread...hreadid=503040 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
Gord That tip was excellent. Thank you very much indeed. Could I trouble you to break the code down for me please? I want toi replicate it in other cells (this time an an adjacent right column) so that when we approve the exception, that will also be time stamped and non volatile. Regards and thanks again for the time and trouble. -- Hillheader ------------------------------------------------------------------------ Hillheader's Profile: http://www.excelforum.com/member.php...o&userid=30644 View this thread: http://www.excelforum.com/showthread...hreadid=503040 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Now() Function
Assumptions made..........
Your users are inserting a time in columns B and C and you want a timestamp in A when time is entered in B. You are entering your approval in column D and want a timestamp in column E when that approval is entered. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Application.EnableEvents = False On Error GoTo enditall 'when entering data in a cell in Col B If Target.Cells.Column = 2 Then '2 is Col B where users enter a start time n = Target.Row If Excel.Range("B" & n).Value < "" Then 'if Bn is not empty An will get a timestamp Excel.Range("A" & n).Value = Now End If End If 'when entering data in Col D If Target.Cells.Column = 4 Then '4 is Col D where you are entering your approval n = Target.Row If Excel.Range("D" & n).Value < "" Then 'if Dn is not empty En will get a timestamp Excel.Range("E" & n).Value = Now End If End If Columns("A:E").AutoFit enditall: Application.EnableEvents = True End Sub Gord On Fri, 20 Jan 2006 15:18:58 -0600, Hillheader wrote: Gord That tip was excellent. Thank you very much indeed. Could I trouble you to break the code down for me please? I want toi replicate it in other cells (this time an an adjacent right column) so that when we approve the exception, that will also be time stamped and non volatile. Regards and thanks again for the time and trouble. Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |