0

At my job we have this CSR sheet that we use for self service.  The idea is you fill each row as a call is taken every day and then email it to our boss.  I put an example of the sheet at the bottom.

However the issue I'm having is that we fill these rows in really fast (every minute or so) and for me and the other users to keep typing the time and date feels redundant (since the date value is, of course, the same all day long).

So I tried using the Now() worksheet function, but the time is dynamic, so every time I start a new line ALL the times update.  I tried reading about VBA here, but I just don't understand.

My question is: How can I have this spreadsheet input the date & time and have it be static in columns C & D, when I put data in columns E, F & G?  So we don't have to manually enter it ourselves?

This is the example:

A B C D E F G H
1 Agent Name Supervisor Date Time           Did you collect callback? Did you HIPPA verify? Did you offer self service?
2 Alex Rebecca 12/14/2022 4:34 PM Yes yes yes
︙   ︙    ︙

 [transcribed from this image]

I'm trying to find a way to optimize the process as much as I can!  Manual entry of the date and time causes production to slow down and mistakes to be made.  Many of the users are not high education or elderly and might have trouble with techniques like Ctrl+; and Ctrl+Shift+;.

Scott - Слава Україні
  • 22,532
Calvin Willis
  • 1

2 Answers2

0

No offense, but: how lazy are you?  If you type Ctrl+; (in Column C), Excel will insert the current date as a static value.  Type Tab (or →) (to move to Column D), then Ctrl+Shift+; to insert the current time.

Reference: Insert the current date and time in a cell (Microsoft Support)

G-Man Says 'Reinstate Monica'
  • 8,029
0

Create this VBA routine:

Private Sub Worksheet_Change(ByVal Target As Range)

Static recursive As Integer
Dim cur_row As Integer
Dim cur_time
Const date_col As Integer = 3
Const time_col As Integer = 4

cur_row = Target.Row
If recursive = 0 And Cells(cur_row, date_col) = "" And Cells(cur_row, time_col) = "" Then
    recursive = 1
    cur_time = Now()
    Cells(cur_row, date_col) = Int(cur_time)
    Cells(cur_row, time_col) = cur_time - Int(cur_time)
    Cells(cur_row, date_col).NumberFormat = "m/d/yyyy"
    Cells(cur_row, time_col).NumberFormat = "h:mm AM/PM"
    recursive = 0
End If



End Sub

It will be called every time any cell is changed. 

  • The assignment statements in the Worksheet_Change routine change the worksheet and therefore trigger recursive calls to the routine.  We use the recursive variable to detect this and prevent recursive execution.
  • Declare variables for the current row and the current date/time.
  • Declare integer constants to designate the Date column (C) and the Time column (D).
  • Set cur_row to the row containing the cell that was changed.
  • If it’s not a recursive call, and the date and time in the current row are blank, then
  • Get the current date/time (an Excel date/time value, which is an integer date plus a fraction of a day).
  • Set the Date cell (in the current row) to today’s date (the integer part of cur_time).
  • Set the Time column to the fractional residue (the time; 0 = midnight, 0.25 = 6 AM, 0.5 = noon, 0.75 = 6 PM, etc.)  Once these values are entered, they are static.
  • Set the formats of the cells.

Note that this will take action in a row only the first time the row is modified (unless the user clears the C and D columns).  If the user changes cells in multiple rows simultaneously, only the first one will be processed.

You might want to consider hiding columns C and D, so the users aren’t confused by them, and don’t need to explicitly skip over them.

How do I add VBA in MS Office? is a useful reference for getting started on the mechanics of working with VBA.

Scott - Слава Україні
  • 22,532