Excel - Pivot Tables: Display Dates Not in Source Data

Question

I am importing a report that has a strict template from a sales monitoring site and I need to create a pivot table to reflect the data. The report generates data on daily sales for the month. The issue: I want a pivot table to reflect the imported data, but display 0's for the dates that are in the future.

For example, my data is being imported and has data from 3/1/16 to 3/21/16 (today's date). I want a pivot table generated from this data to display the full month of March, with 0's for the dates after today. Is this possible?

Answer 1

As far as I know, pivot tables need a data source to read from. So if you want the rest of month to show up with 0's, you will need to create dummy data for it. So fill out the data source with the rest of the month and 0's in all the columns. Then it will show up in your pivot table (as long as those cells are part of the pivot table's data source).

Answer 2

Without seeing the full layout you have in mind, it's difficult to be precise with an answer. Based upon what I can gather from your original post, I'd recommend inserting a column into your source data, let's call that column B.

Let's say your source data dates are in column A. Let's assume you have a header row in row 1 and column A is formatted as Date.

Insert a formula into B such that:

=IF($A2>NOW(), 0, $A2)

This will give you a column that either returns zero for future dates or the source data date for dates in the past/present. Update your pivot to reflect the new column B instead of Column A. The challenge you will have is that Excel will attempt to present 0 as a date (since the rest of the column is a date), showing 01/00/1900 instead of zero.