If you have a call center and track incoming callers and their call times, you might store all of that information in Microsoft Excel. The sheet would store caller identification and timestamp values.
Reporting them though, won’t be as simple as printing off a list each day because callers may call several times throughout a single day. That’s what will be meaningful to those needing the information.
In this tutorial, you’ll learn what a timestamp is and then how to use Excel functions MIN() and MAX() to return the first and last call for the day from timestamps. Then, you’ll build a grouped set of records that returns the first and last call for each caller.
I’m using Microsoft 365 on a Windows 10 64-bit system. A few of the functions used are available only in Microsoft 365 and Excel for the web.
SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)
How to return the first time value in Excel
A timestamp is a combined date and time that marks a specific point in time. If you change the cell’s format to General or Number, you will see a number instead of a date. The number’s integer represents the date, and the decimal value represents the time within that date.
For example, Figure A shows a column of timestamps formatted to display as dates and times. The column next to it shows the underlying values for each timestamp.
Figure A
Now, let’s suppose your help desk call center tracks calls by caller and the time the call comes in. At the end of the day, you want to know the first and last calls of the day. The simple demonstration sheet shown in Figure B lists the calls in order, so it’s easy to see the first and last call, but that won’t always be the case, depending on how personnel enter the call records.
Figure B
It would be easy for one operator to enter a call record a few minutes later than received, and then your records would no longer be in order, by time. So, we’ll not count on that in our solution.
Fortunately, Excel’s MIN() function will return the earliest (smallest time value) call of the day. This simple function requires only one argument and that’s the range or structured reference that contains the values we’re evaluating.
The function
=MIN(TableCalls[Call Date])
uses structured reference because the data range is an Excel Table object named TableCalls. If you’re evaluating a normal data range, you’d use the reference
=MIN(C3:C12).
The function
=VLOOKUP(F3,TableCalls,1)
returns the client who made that first call, as shown in Figure C. The structured reference, TableCalls is the Table’s name. F3 refers to the first call time (to the right), and the argument 1 returns the corresponding value in the first column of TableCalls.
Figure C
Now let’s return the last call of the day.
How to return the last time value in Excel
Once working through the functions to return the earliest call and the client making that call, doing the same for the day’s last call is simple. We’ll use MAX() to return the last call and another XLOOKUP() function to return the client making that call.
Figure D shows both functions:
E5: =VLOOKUP(F5,TableCalls,1)
F5: =MAX(TableCalls[Call Date])
Figure D
The XLOOKUP() function returns the client that made the last call by finding the time value in F5 and returning the corresponding value from the Customer ID column. The MAX() function returns the latest (the largest time value) call from the time values in the Call Date column, C5:C12.
If you’re following along and the time values in F3 and F5 are displaying both the date and time, you can reformat those cells to display only the time.
First, select F3, and on the Home tab, click the Format dropdown in the Number group. Choose Time from the dropdown list. Repeat these steps for cell F5.
That was easy and works if all you need is the first and last call of the day. Let’s suppose you want a record for each client that returns the first and last call of the day if that client made more than one call. This requirement is more complex.
How to return a caller and their calls in one record in Excel
It’s possible that management might want to see a list of all clients with their first and last call if that client made more than one call. You won’t meet that request with a few simple functions, but you can do it (Figure E).
Figure E
The first step is to return a unique list of Customer IDs. To do so, enter the following dynamic array function into H3
=SORT(UNIQUE(TableCalls[Customer ID]))
This function returns a sorted unique list of Customer ID values as a dynamic array. That means there’s only one expression, and it’s in H3. The rest of the column is a spill range — the results needed to fulfill the expression.
To return the first call for each Customer ID, enter the following function into I3 and copy it to the remaining cells:
=XLOOKUP($H3,TableCalls[Customer ID],TableCalls[Call Date])
This function returns the first call for the corresponding Customer ID in column H.
To return the last call for each Customer ID, enter the following function into J3 and copy it to the remaining cells:
=XLOOKUP($H3,TableCalls[Customer ID],TableCalls[Call Date],”No Results”,,-1)
The last argument, -1, performs the search from the bottom up, which is why it can return the last call. If you sort your calls in descending order, you’ll need to modify both functions by removing it from the one in J3 and adding it to the one in I3.
This setup works, but only one customer called more than once, so the functions duplicate the first call as the last call. The result is worse than distracting, it’s confusing, so let’s add a conditional format that will hide duplicate times in the last call column.
First, select J3:J9, and on the Home tab, click Conditional Formatting in the Styles group and select New Rule from the dropdown. In the resulting dialog, click the last choice in the top pane, Use a Formula to Determine Which Cells to Format.
In the formula control, enter =$J3=$I3 (Figure F). Click Format, click the Font tab, choose white from the palette, and click OK twice to return to the sheet.
Figure F
As you can see in Figure G, only the last time value for Customer ID 101 is visible. The other values are there, but you can’t see them because the font is the same color as the background. I don’t like to hide things, but because this will update on a routine basis, it seems a convenient solution.
Figure G
Stay tuned
It seems like a lot of work, but all the functions we used are easy to implement. The one hiccup is that you can’t use dynamic array functions in Table objects. That means you must update the functions in columns I and J and the conditional formatting reference, when necessary. For that reason, I’ll show you how to accomplish the same thing with a PivotTable in a future article.
If you’re not familiar with XLOOKUP(), you can read How to use the newish XLOOKUP() dynamic array function in Excel. To learn more about UNIQUE(), read How to use the UNIQUE() function to return a count of unique values in Excel.
