Now() Function

[Hillheader]

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

[Stanley]

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
>
>

[Hillheader]

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

[Gord Dibben]

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
<[email protected]> 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]

Gord

I do not pretend to understand this but I will give it a go!!!

Many Many Many Thanks!!!

[Hillheader]

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]

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
<[email protected]> 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