I'm looking for a formula that would look at cell A1 which identifies the month and year for the particular worksheet and then have it look at the anniversary date of the employee affiliated with the particular row and then add up the hours up to the anniversary date (if applicable) and total it in column "BN". I'd like a similar formula in column "BO", but would add only the post anniversary hours (if applicable to the employee for the given month).
I've attached a sample workbook to help explain what I need. Complicating this formula is the fact that I have duplicate columns with similar dates, which would need to be added if the header number (row 1) is less than or equal to the anniversary date or greater than the anniversary date for the second formula required. Though the sample workbook only reflects one sheet for the month of January, my actual workbook would have a sheet for each month and on each sheet cell A1 would reflect the 1st day of that given month for that sheet. I hope this all makes sense.
*edited to add: my initial sample workbook example had incorrectly stated to include up through the anniversary date, when the pre-anniversary data should only include date up to the date. A revised workbook was attached.
Any assistance would be greatly appreciated.
Bookmarks