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


When analyzing organized data, bypass complex functions and utilize a Microsoft Excel PivotTable.

Girl in the park holding a smartphone with Microsoft Office Excel app on the screen. Rustic wooden table. Rio de Janeiro, RJ, Brazil. January 2022 Image: Diego/Adobe Stock The short article How to return first and last times from timestamps in Microsoft Excel usages vibrant variety functions to return the first and last time from a timestamp in Microsoft Excel. It works and along the method you discover a fair bit. Nevertheless, it’s more work than essential if you do not mind reporting the same data in a Microsoft Excel PivotTable. In this tutorial, I’ll reveal you a simple approach to report the first and last times from a timestamp in Excel utilizing a PivotTable. We’ll work with the same information from the earlier post. You can download the demonstration for this Excel tutorial.

SEE: Google Office vs. Microsoft 365: A side-by-side analysis w/checklist( TechRepublic Premium)I’m utilizing Microsoft 365 on a Windows 10 64-bit system, however you can use earlier variations of Excel . Excel for the web supports PivotTables. How

to produce a PivotTable in Excel A timestamp is a combined date and time that marks a specific point in

time. If you alter 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< img src=""alt ="" width="770 "height="477 "/ > The numbers in column D are the underlying values for the timestamp worths in column C. Must-read Windows coverage

Now let’s suppose you run an aid desk. At the end of the day, you report each call by client and the time the call came in. Customers can call more than as soon as, and you report the very first and last call by customer. The quickest method to do so is to utilize a PivotTable.

To insert a PivotTable based on the call center data displayed in Figure A, click anywhere inside the Table and do the following: Click the Insert tab. 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. In the resulting dialog, click Existing Worksheet. Click inside the Place control

  1. and after that click E2(Figure
  2. B). Click OK. Figure B< img src =""alt =" "width="770"
  3. height =”367″/ > Setup the PivotTable. Excel will place
  4. a blank PivotTable frame. Using Figure C as a guide, drag the Client ID field to
  5. the Rows list. The

resulting PivotTable has one column, an organized set of Consumer IDs. Now let’s include the very first and last call columns. Figure C Include fields to the PivotTable frame using the Fields pane. To return the very first require each consumer, do the following: 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 consumer, 101, is the only consumer to have more than one call. To change the function, right-click anywhere inside the newly included call column and select Worth Field

Settings from the resulting submenu. In the resulting dialog

  1. , select minutes in the Summarize Value Field By list(Figure E). Figure D Include the Call Date field to the Values list. Figure E Pick the MIN function for the brand-new column. Figure F

The minutes function returns the very first timestamp for each group. As you can see in Figure F, the brand-new column returns the time of the first require each client. To format the worths in the brand-new column, choose and right-click the selection, pick Number Format from the resulting submenu and after that choose Time in the Classification list and click OK.

You may also change the header text to”First Call “so it’s more meaningful to audiences. To include the last call time, do the very 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 The finished PivotTable displays the first and last call for each consumer.

You could

stop here, but only one client had more than one call. Using the same time for both the very first and last call is puzzling. How to add a conditional format to the PivotTable If you think showing the very same time as both the first and

time may puzzle viewers, you can include a conditional format rule to display just the first call time when both the first and last call times are the very same. To add the conditional format, do the following: Select the last call values in the PivotTable. Click the House tab and

then click Conditional Formatting in the Designs group and select New Rule from the dropdown.

  • In the top pane, choose All Cells Showing”Last Call”Worths For “Customer ID “. In the center pane, click the last option, Use
  • a Formula to Figure Out Which Cells to Format. In the formula control, get in=
  • $G3 =$F3 (Figure H). Click Format, click the Font style tab, pick white from the combination, and click okay
  • twice to return to the sheet. Figure H Go into the basic conditional guideline.

    Figure I

    After using the conditional format guideline, just one client, 101, shows a very first and last call time.

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

    A PivotTable is an easy method to show the very first and last time from a timestamp in Excel. If a PivotTable is acceptable reporting, it’s the simplest service.


  • Leave a Reply

    Your email address will not be published. Required fields are marked *