How to return first and last time from a timestamp in Microsoft Excel

If you have a call center and track incoming callers and the duration of their calls, you can store all this information in it Microsoft Excel. The sheet will store caller ID and timestamp values.

However, reporting them will not be as simple as printing out a list each day, as callers may call multiple times in a day. This is what will be relevant to those who need the information.

In this tutorial, you’ll learn what a timestamp is and then how to use Excel’s MIN() and MAX() functions to return the first and last calls of the day from the timestamp. You will then create a grouped recordset that returns the first and last call for each caller.

I’m using Microsoft 365 on a Windows 10 64-bit system. Some of the features used are only available in Microsoft 365 and Excel for the web. You are downloading the demo for this Excel tutorial.

SEE: Windows, Linux, and Mac Commands Everyone Should 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 format to General or Numeric, you’ll see a number instead of a date. The integer value of the number represents the date and the decimal value represents the time within that date.

For example, Figure A displays a column of timestamps formatted to appear as dates and times. The column next to it shows the base values ​​for each timestamp.

Figure A

Now suppose your help desk call center tracks calls by caller and time of call. At the end of the day, you want to know the first and last call of the day. The simple demo sheet shown in Figure B lists calls in order so it’s easy to see the first and last call, but this won’t always be the case depending on how staff enter the call records.

Figure B

It would be easy for an operator to enter a call record a few minutes later than received and then your records would no longer be in time order. So we will not rely on it in our decision.

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 is the range or structured reference that contains the values ​​we are evaluating.

The function

=MIN(TableCalls[Call Date])

uses a structured reference because the data range is an Excel table object called TableCalls. If you were evaluating a normal range of data, you would use the reference

=MIN(C3:C12).

The function

=VLOOKUP(F3,TableCalls,1)

returns the client that made this first call, as shown in Figure C. The structured reference, TableCalls is the name of the table. F3 refers to the time of the first call (on 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 you work with the functions to return the earliest call and the customer making that call, doing the same for the last call of the day is easy. We’ll use MAX() to return the last call and another XLOOKUP() function to return the customer 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 customer who 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 last (largest time value) call from the time values ​​in the CallDate column, C5:C12.

If you follow along and the time values ​​in F3 and F5 show both the date and time, you can reformat those cells to show only the time.

First, select F3 and on the Home tab, click the Format drop-down menu in the Number group. Select Time from the drop-down list. Repeat these steps for cell F5.

This was easy and works if all you need is the first and last call of the day. Suppose you want a record for each customer that returns the first and last call of the day, if that customer made more than one call. This requirement is more complex.

How to return a caller and their calls in a single record in Excel

Management may want to see a list of all customers with their first and last call if that customer has made more than one call. You won’t satisfy this 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 this, enter the following dynamic array function in H3

=SORT(UNIQUE(Table calls[Customer ID]))

This function returns a sorted unique list of customer ID values ​​as a dynamic array. This means there is only one expression and it is in H3. The rest of the column is an overflow range—the results needed to execute the expression.

To return the first call for each customer ID, enter the following function in 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 in 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, so it can return the last call. If you sort your calls in descending order, you’ll need to change both functions, removing it from the one in J3 and adding it to the one in I3.

This setup works, but only one customer calls 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 drop-down menu. In the dialog box that appears, click the last selection in the upper window, Use a formula to determine which cells to format.

In the formula control, type =$J3=$I3 (Figure F). Click Format, click the Font tab, select 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 client 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 since this will be updated routinely, it seems like a convenient solution.

Figure G

Stay on the line

It seems like a lot of work, but all the features we used are easy to do. The only problem is that you can’t use dynamic array functions on table objects. This means that you need to update the functions in columns I and J and the conditional formatting reference when necessary. For this reason, I will show you how to achieve the same with a pivot table in a future article.

If you are not familiar with XLOOKUP(), you can read How to use the new XLOOKUP() dynamic array function in Excel. To learn more about UNIQUE(), read How to use the UNIQUE() function to return a number of unique values ​​in Excel.