I am creating a timesheet and have it all working except one function - to be able to average the hours worked for the previous 9 weeks based on a holiday date. (Sounds strange, but Provincial regulations!!!)
In the Excel Spreadsheet, I have a tab for each month, and a tab for "Defaults".
On each months Tab
C8:AG8 has the dates
C16:AG16 has the hours worked on each of those dates.
Note: if a date is not worked, then 0 (zero) is recorded in row 16 for that date, therefore the average calculation has to avoid including those dates, i.e the average has to not include zeros.
On the Defaults Tab, I list the holiday and the holiday date which is updated each year - Some holidays are on the 3rd Monday of the month which means the date changes each year.
I have not be able to work out how to average the hours worked across multiple sheets.
The averageifs works, in that it has the ability to use criteria for the start and end dates and ignore 0 (zeros). However I have not been able to get the range to work across multiple sheets.
This is some if what I have tried.
- If I was trying to average between two dates on one tab - This works
Jul is the tab name
C16:AG16 holds the hours worked for each day worked.
C8:AG8 holds the date in date format.
B33 is the start date (the holiday - (minus) 63 days (9 weeks)
B24 is the end date (the holiday)
However when I go and try and expand the above equation to work over multiple tabs, it does not work.
Any suggestion on how to average over multiple tabs using a start and end date, ignoring zeros/Null values would be appreciated.
Thank you all for help.
BTW - It will be used in Excel 2013 & 2016