While working with a large Microsoft Excel, sometimes we see that time and date are simultaneously situated in a cell which is a common scenario. We need to remove time from the date. We can easily do that from our dataset. Today, in this article, we’ll learn how to remove time from date in the pivot table in Excel effectively with appropriate illustrations.
Remove Time from Date in Pivot Table in Excel: with Easy Steps
Let’s say, we have a dataset that contains information about several dates and times in Column B. We will remove time from the date of our Pivot Table step-by-step methods. Here’s an overview of the dataset for today’s task.
In this article, we will use three easy steps to demonstrate how you can remove time from date in pivot table. Let’s follow the steps below to learn!
Step 1: Create a Pivot Table in Excel
From our dataset, firstly, we will make a pivot table then we will remove time from Column B. To create a pivot table, follow the instructions below.
- First of all, from your Insert ribbon, go to,
Insert → Tables → PivotTable → From Table/Range
- After clicking on the From Table/Range option, a PivotTable from table or range dialog box will appear in front of you. From that dialog box, firstly, from our dataset select cells $B$4:$B$14 in the Table/Range box, secondly, check the Existing Worksheet At last, press OK.
- Hence, create a PivotTable Fields. Now, from the PivotTable Fields option, check the Date and Time, Quarters, and Years options.
- After that, a PivotTable with heading Row Labels will appear in front of you. Our PivotTable data is grouped. Now, we will ungroup our PivotTable To do that, firstly, select cell D5 and then press right-click on your Mouse. Further, a window pops up. From that window, press on the Ungroup option.
- Hence, you will be able to ungroup our PivotTable data which has been given in the below screenshot.
Step 2: Go to Format Cells Command to Remove Time from Date in Pivot Table in Excel
- First, select our pivot table data to remove time from the date. From our dataset, we will select cells D4 to D15.
- Hence, press Ctrl + 1 on your keyboard After that, a Format Cells dialog box will appear in front of you. From the Format Cells dialog box, firstly, select Number option, secondly, select Date from Category option. Thirdly, select 03-14-12 from the Type menu. At last, press OK.
Step 3: Change Format to Remove Time from Date in Pivot Table in Excel
- After completing the above process, you will be able to remove time from the pivot table that has been given in the below screenshot.
Things to Remember
👉 To create Format Cells, press Ctrl + 1 on your keyboard simultaneously.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
I hope all of the suitable methods mentioned above to remove time from date in the pivot table will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.