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

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

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

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

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

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

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