When analyzing organized data, bypass complex functions and utilize a Microsoft Excel PivotTable.
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
the decimal value represents the time within
that date, as you can see in Figure A. Figure A< img src="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/08/PTFirstLastTimestamp_A-770x477.jpg"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
- and after that click E2(Figure
- B). Click OK. Figure B< img src ="https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2022/08/PTFirstLastTimestamp_B-770x367.jpg"alt =" "width="770"
- height =”367″/ > Setup the PivotTable. Excel will place
- a blank PivotTable frame. Using Figure C as a guide, drag the Client ID field to
- 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
- , 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.
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.
