I'm creating a pivot table that summarizes energy usage recorded at a site vs. what the billing company says was actually used at the site.
I've ran into trouble having both sets of data display, because the billing data is in a monthly format. Where the recorded data is reported every 15 minutes. I've created a look-up calendar table to use for both sets of data using the TIME(0,15,0) function to create a long list of dates for the year, and this works beautifully for relating the two data sets.
However, billed data breaks down on the pivot table after a few months, showing up as "blank". I've determined the cause to be that my lookup table contains floating point rounding error due to being created by a function, where the monthly dates are exact integers entered by hand.
I can get around this by copying and pasting the value of the date from the first of the month in the look-up calendar into the date for the billed data, but this is time consuming. There has to be a better way of creating a calendar lookup table for this purpose.
Any advice? I've included the worksheet if you need to look at it to tell what I've done.
