Having a personal spreadsheet document to capture solved tasks (date, customer, project, detail, from time, to time --> consumed hours), I am already using the pivot table on another sheet to group spent hours for each day (independently on customers, project id, details). The result looks like this:
The column A contains the date, the column B shows Sum - hours (1). Notice also total sum at the bottom, marked as (5). I have added the C (2) as working over 8 hours (green) or under 8 hours (pink). The D (3) flags Saturdays (here "sobota") and Sundays. The E is like C but showing only values for working days.
Just now, I add the extra field to sum the values for one month, in the red rectangle:
The problem is that I have to check the ranges (rows) that are being summed. Also, I have to make the correction for summing also the "Total" (5).
The number of days in the source pivot is never fixed. This is because some days are not recorded. So, not every day is listed. Also, the leap year has one extra day.
I was thinking first about automatic detection of days (rows) for each month; so, each of the red rectangles for the month could determine the source range. Later, the following question came to my mind...
Can another pivot on the sheet be used to calculate the "red-rectangle values"?
What would be your approach/recommendation to solve the problem?