How to convert pivot results into continuous list of days?

Question

I have flow measurements per 15 minutes. There are days with no data. I have used pivot table to get the daily average of flow. How can I convert pivot results into continuous list of days as illustrated below?

Answer 1

You can create a pivot table, drag the dates into the row panel. and the numeric values into the values panel.

You can then click on any date in the pivot table and group. You may want to group by Year, Month and Day.

Be aware, though, that a pivot table only shows existing data. That means that if your source data table has values for Jan-1 and Jan-3 but not for Jan-2, then the pivot table will not list Jan-2 at all.

If you want all calendar days to be listed, you may need to ensure that your source data contains at least one data point for each calendar day. You may need to add missing days to the source data manually.

Pivot tables do not interpolate missing days.

If you want to create a new table, based on the pivot table, you can use formulas to look up the actual date value and return the number (a simple Vlookup() will work). If the Vlookup in the row for Jan-2 returns an error, since the pivot table does not contain a value for that day, you can wrap the Vlookup in an IFError function and provide in the second argument what to return in an error situation.

What exactly that needs to be is up to you. You need to decide the business logic first and put it into words. Then a formula can be found. For example, you can return the previous row's value, or an average of the last ten rows, or a static value that lives in cell ZZ333.

Edit: You can avoid the grouping if the source data has only dates, not dates with times. To achieve that, add a column to the source data and work out only the date of the current date/time value. The formula for that is =int(A1). Use that date-only column in the pivot table and you don't need to group.