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

When analyzing grouped data, bypass complex functions and use a Microsoft Excel PivotTable.

The article How to return first and last times from timestamps in Microsoft Excel uses dynamic array functions to return the first and last time from a timestamp in Microsoft Excel. It works and along the way you learn quite a bit. However, it’s more work than necessary if you don’t mind reporting the same data in a Microsoft Excel PivotTable.

In this tutorial, I’ll show you an easy method to report the first and last times from a timestamp in Excel using a PivotTable. We’ll work with the same data from the earlier article.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web supports PivotTables.

How to create a PivotTable 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, you will see a number instead of a timestamp. The number’s integer represents the date, and the decimal value represents the time within that date, as you can see in Figure A.

Figure A

Now let’s suppose you run a help desk. At the end of the day, you report each call by client and the time the call came in. Clients can call more than once, and you report the first and last call by client. The quickest way to do so is to use a PivotTable.

To insert a PivotTable based on the call center data shown in Figure A, click anywhere inside the Table and do the following:

1. Click the Insert tab.
2. In the Tables group, click PivotTable and then choose From Table/Range in the dropdown. In this case, Excel will base the PivotTable on TableCalls2.
3. In the resulting dialog, click Existing Worksheet.
4. Click inside the Location control and then click E2 (Figure B).
5. Click OK.

Figure B

Excel will insert a blank PivotTable frame. Using Figure C as a guide, drag the Customer ID field to the Rows list. The resulting PivotTable has one column, a grouped set of Customer IDs. Now let’s add the first and last call columns.

Figure C

To return the first call for each customer, do the following:

1. Drag Call Date to the Values list in the Fields pane. By default, Excel will return the count of calls for each group (Figure D). The first customer, 101, is the only customer to have more than one call.
2. To change the function, right-click anywhere inside the newly added call column and choose Value Field Settings from the resulting submenu.
3. In the resulting dialog, select MIN in the Summarize Value Field By list (Figure E).

Figure D

Figure E

Figure F

As you can see in Figure F, the new column returns the time of the first call for each customer. To format the values in the new column, select and right-click the selection, choose Number Format from the resulting submenu and then choose Time in the Category list and click OK. You might also change the header text to “First Call” so it’s more meaningful to viewers.

To add the last call time, do the same thing but instead of choosing MIN, choose MAX. Format the values and change the header text to Last Call, as shown in Figure G.

Figure G

You could stop here, but only one customer had more than one call. Using the same time for both the first and last call is confusing.

How to add a conditional format to the PivotTable

If you think displaying the same time as both the first and time might confuse viewers, you can add a conditional format rule to display only the first call time when both the first and last call times are the same.

To add the conditional format, do the following:

1. Select the last call values in the PivotTable.
2. Click the Home tab and then click Conditional Formatting in the Styles group and select New Rule from the dropdown.
3. In the top pane, choose All Cells Showing “Last Call” Values For “Customer ID”.
4. In the middle pane, click the last choice, Use a Formula to Determine Which Cells to Format.
5. In the formula control, enter =$G3=$F3 (Figure H).
6. Click Format, click the Font tab, choose white from the palette, and click OK twice to return to the sheet.

Figure H

Figure I

As you can see in Figure I, the conditional format hides the last call time when it’s the same as the first call time. You can remove the grand total row, but it displays the first and last call of the day, so it’s meaningful information.

A PivotTable is an easy way to display the first and last time from a timestamp in Excel. If a PivotTable is acceptable reporting, it’s the easiest solution.