Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)

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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Remove Time from Date in Pivot Table in Excel

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.

remove time from date in excel pivot table

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

Create a Pivot Table in Excel

  • 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.

Create a Pivot Table in Excel


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 cell D4 to D15.

Go to Format Cells Command to Remove Time from Date in Pivot Table in Excel

  • 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.

Go to Format Cells Command to Remove Time from Date in Pivot Table in Excel


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.

Give Format to Remove Time from Date in Pivot Table in Excel

Read More: VBA to Remove Time from Date in Excel (3 Methods)


Things to Remember

👉 To create Format Cells, press Ctrl + 1 on your keyboard simultaneously.


Conclusion

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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo