Graphed LogoGraphed
Login

How to Remove Time from Date in Excel Pivot Table

Cody Schneider•

Excel pivot tables are brilliant for summarizing data, but they can get finicky when it comes to dates. If your source data includes both a date and a timestamp (like "1/15/2024 08:30 AM"), a pivot table will treat every unique time as a separate entry. This article will show you a few simple and effective methods to remove the time from your dates, so you can group and analyze your data correctly.

Why Does Time Appear in My Pivot Table in the First Place?

Before we jump into the solutions, it helps to understand the root of the problem. Excel doesn't see dates and times the way we do. It stores all date and time information as a single serial number.

  • The whole number part (to the left of the decimal) represents the date. For example, the number 45321 represents January 1, 2024.

  • The decimal part (to the right of the decimal) represents the time of day. For example, .5 represents noon (12:00 PM), .25 is 6:00 AM, and so on.

So, a value like 45321.75 in Excel's memory translates to January 1, 2024, 6:00 PM.

When you import data from another system, it often includes this precise timestamp. In a regular spreadsheet, this might not matter. But in a pivot table, 'Jan 1, 8:00 AM' and 'Jan 1, 10:00 AM' are treated as two different values. Your goal to summarize sales for January 1st is impossible because your data is not grouped by day. To fix this, we need to strip away that decimal part.

Method 1: Use the Pivot Table 'Group' Feature (The Easiest Fix)

The fastest way to solve this problem is by using a feature built directly into pivot tables. This method doesn't change your source data, it just tells the pivot table how to display it.

Here’s how to do it:

  1. Create your pivot table and drag the date field into the Rows or Columns area. You'll likely see a long list of individual dates with timestamps.

  2. Right-click on any cell containing a date and time within the pivot table.

  3. From the context menu, select Group.

  4. A 'Grouping' dialog box will appear. Here, you can choose how to bucket your date values. Deselect any time-based increments like Minutes, Hours, and Seconds.

  5. Make sure Days is selected. You can also select Months, Quarters, and Years if you need those higher-level groupings.

  6. Click OK.

Your pivot table will instantly refresh and condense all the timestamps into neat, single-day rows. All entries for January 1st will now be summarized under a single "Jan 1" label.

Quick Tip: What if I get a "Cannot group that selection" error?

This is a common error, and it almost always means there is a problem with your source data column. The Group feature requires that every single cell in that column contains a valid date. If even one cell is blank or contains text (like "N/A"), the function will fail. To fix this, go back to your source data, apply a filter to your date column, and look for empty cells or text values that need to be corrected or removed.

Method 2: Create a 'Helper Column' with a Formula (The Most Reliable Method)

While the grouping feature is great for a quick fix, it can sometimes be inflexible. A more robust and permanent solution is to create a new column in your original dataset that contains only the date portion of your timestamp. This gives you a clean field to use in your pivot table and any other analysis you might do.

We'll use a simple Excel function for this. The INT function is perfect because it chops off the decimal part of any number, which is exactly what we want to do to our date-time serial numbers.

Steps to Create a Helper Column with INT:

  1. Go to your source data table or range.

  2. Right beside your original date column (let's assume it's column A), insert a new, empty column. Let’s call it "Date Only".

  3. In the first data cell of this new column (e.g., cell B2), type the following formula and press Enter:

    =INT(A2)

    This formula looks at the date and time in cell A2 and returns only the integer portion (the date).

  4. Click on cell B2 again, grab the small square at the bottom right corner (the fill handle), and drag it down to apply the formula to your entire column of data.

  5. Important Final Step: Format as Date. Your new column will probably display as numbers (e.g., 45321). This is correct, but not very readable. Select the entire "Date Only" column, right-click, choose Format Cells, and from the Number tab, select Date and pick a format you like (e.g., "3/14/2012").

Update Your Pivot Table with the New Column

Now that you have a clean date column, go back to your pivot table.

  • Right-click anywhere inside the pivot table and select Refresh.

  • The new "Date Only" field will appear in your PivotTable Fields list.

  • Remove the original date-time field from the Rows/Columns area.

  • Drag your new "Date Only" field into the Rows/Columns area.

That's it! Your pivot table now uses the clean data, and grouping will work perfectly without any fuss.

Note: The TRUNC function (=TRUNC(A2)) does the exact same thing as INT for positive numbers, so you can use it interchangeably here. Both are great options.

Method 3: Use Power Query for Automatic Cleaning (The "Set It and Forget It" Method)

If you're dealing with data that you need to refresh regularly (like a weekly sales report export), creating a helper column every single time can get tedious. This is where Power Query shines. Power Query is Excel's built-in data transformation engine, and it can automate the entire cleaning process for you.

Here’s a basic workflow to clean your dates with Power Query:

  1. Select your data range, and from the Data tab on the Excel ribbon, click From Table/Range. This will open the Power Query Editor in a new window.

  2. In the editor, find and select your date-time column.

  3. Go to the Transform tab on the Power Query ribbon.

  4. Find the Date & Time Column group. Click the Date dropdown menu and then select Date Only.

  5. You'll see the values in the column instantly update, stripping away the time information. The formula bar at the top will show the M code used, something like Table.TransformColumns(#"Changed Type",{{"datetime", DateTime.Date, type date}}).

  6. Once you're satisfied, go to the Home tab and click the Close & Load button.

Excel will then load your clean data into a new worksheet, formatted as a proper Excel table. You can build a pivot table directly from this newly created green table.

The real magic happens the next time you get updated data. Just paste the new data into your original source tab, go to the Data tab, and click Refresh All. Power Query will automatically run all your cleaning steps on the new data and update your pivot table in seconds. No more manual formulas needed!

Final Thoughts

As you can see, dealing with unwanted timestamps in Excel pivot tables is a manageable problem with several solutions. For a one-off report, the Group feature is the quickest fix. For more reliable, reusable analysis, creating a helper column with the INT or TRUNC formula is a rock-solid approach. And if you’re looking to automate your reporting process entirely, getting comfortable with Power Query will save you an incredible amount of time in the long run.

While mastering these Excel tricks is empowering, it also shows how much time can go into just preparing data before analysis can even begin. At Graphed, we created a tool to eliminate this manual work entirely. Instead of struggling with formulas or cleaning steps, you can connect sources like Google Analytics, Shopify, or HubSpot, and simply ask a question in conversational language, like "Show me revenue by day for the last 90 days." Graphed generates a clean, real-time dashboard instantly, turning hours of data prep into a 30-second conversation.