How to return first and last times from timestamps in Microsoft Excel

If you have a call center and track incoming callers and their call times, you can store all this information in Microsoft Excel. The leaf would store caller identification and timestamp values.

However, reporting isn’t as easy as printing out a list every day, since callers can call multiple times a day. That is what matters to those who need the information.

In this tutorial, you will learn what a timestamp is and how to use Excel’s MIN() and MAX() functions to return the first and last call for the day from timestamps. You then create 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. Some of the features used are only available in Microsoft 365 and Excel for the web. You download the demo for this Excel tutorial.

SEE: Windows, Linux, and Mac Commands Everyone Needs to Know (Free PDF) (TechRepublic)

How to return first time value in Excel

A timestamp is a combination of date and time that marks a specific point in time. If you change the format of the cell to General or Number, a number is displayed instead of a date. The integer of the number represents the date, and the decimal represents the time within that date.

For example, Figure A shows a column of timestamps formatted to display as a date and time. The column next to it shows the underlying values ​​for each timestamp.

Figure A

Now suppose your helpdesk call center tracks calls by caller and the time the call came in. At the end of the day you want to know the first and last call 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’s not always the case depending on how the staff enters 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 over time your records would be out of order. So we don’t rely on that in our solution.

Fortunately, Excel’s MIN() function returns the earliest (minimum time value) call of the day. This simple function takes only one argument, and that’s the range, or structured reference, that holds the values ​​we’re evaluating.

The function

=MIN(table calls[Call Date])

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

=MIN(C3:C12).

The function

=VLOOKUP(F3,table views,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 first call time (on the right) and the argument 1 returns the corresponding value in the first column of TableCalls.

Figure C

Now let’s call back the last call of the day.

How to return last time value in Excel

After working through the features of returning the earliest call and the client that made that call, it’s easy to do the same for the last call of the day. We use MAX() to return the last call and another XLOOKUP() function to return the client that made that call.

Figure D shows both functions:

E5: =VLOOKUP(F5,table views,1)

F5: =MAX(table calls[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 client ID column. The MAX() function returns the last call (the largest time value) from the time values ​​in the Call Date column, C5:C12.

If you follow that 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 you only need the first and last call of the day. Suppose you want a record for each client that returns the first and last call of the day when that client made more than one call. This requirement is more complex.

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

It is possible that management would like to see a list of all customers with their first and last call if that customer has made more than one call. A few simple functions won’t get you that wish, but you can (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(TableCalls[Customer ID]))

This function returns a sorted unique list of customer ID values ​​as a dynamic array. That is, there is only one expression, and that is in H3. The rest of the column is an overflow area – the results needed to satisfy 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],Table Calls[Call Date])

This function returns the first call to 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 into the remaining cells:

=XLOOKUP($H3,TableCalls[Customer ID],Table Calls[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 will need to change both functions by removing them from the one in J3 and adding them to the one in I3.

This setup works, but only one customer called more than once, so the features 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 hides duplicate times in the last call column.

First, select J3:J9, on the Home tab, in the Styles group, click Conditional Formatting and select New Rule from the drop-down menu. In the resulting dialog box, click the last option at the top, Use a formula to determine which cells to format.

Enter in the formula control =$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 it 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 hiding things, but as this is routinely updated it seems like a convenient solution.

Figure G

stay tuned

It seems like a lot of work, but all the features we used are easy to implement. The only downside is that you can’t use dynamic array functions in Table objects. That means you need to update the functions in the I and J columns and the reference to the conditional formatting as needed. For this reason, in a future article, I will show you how to achieve the same thing with a PivotTable.

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