So we do a lot of work that involves calculating how many IRS working hours an employee qualifies for in a given year / employment window. Essentially, it's 8 hours for every weekday in the year that they work. What I'm trying to create is a quick formula that can reference a date range using a Start Date value from one cell and an End Date value in another cell. So if it's say 4/1/2020 and 11/15/2020, I want it to report back a total of 8 hours per weekday in that period.
My current plan is to create an index with three columns - Date, Day, Hours - and basically prefill out a chart with 8 hours on qualified days, and then sum values in a range from that Start Date cell to the End Date cell. What I can't figure out is how to search for say cell A2's content in a separate sheet like vlookup, but instead of returning the value in index 3 of the array I want it to report back the cell name of that cell in the third column so I can use it in a SUM formula. That way I can SUM the values between Start Date and End Date on my table directly. This will let me put an Active row at the very end of the table to account for employees who work through the full year, since current employees don't have End Dates.
Below is a rough example of the index I'm working from, since I can't upload a direct workbook on the mac. I can't figure out a formula that I can use to reference the start and end dates provided and sum the values for those dates according to my index table. Alternate suggestions for how to accomplish this are welcome, but ideally help with the formula I need is preferred.
My current plan is to create an index with three columns - Date, Day, Hours - and basically prefill out a chart with 8 hours on qualified days, and then sum values in a range from that Start Date cell to the End Date cell. What I can't figure out is how to search for say cell A2's content in a separate sheet like vlookup, but instead of returning the value in index 3 of the array I want it to report back the cell name of that cell in the third column so I can use it in a SUM formula. That way I can SUM the values between Start Date and End Date on my table directly. This will let me put an Active row at the very end of the table to account for employees who work through the full year, since current employees don't have End Dates.
Below is a rough example of the index I'm working from, since I can't upload a direct workbook on the mac. I can't figure out a formula that I can use to reference the start and end dates provided and sum the values for those dates according to my index table. Alternate suggestions for how to accomplish this are welcome, but ideally help with the formula I need is preferred.
| Start Date | End Date | Total Hours |
| 1/1/2020 | 1/6/2020 | XX |
| 1/6/2020 | Active | XX |
| Date | Day | Hours |
| 1/1/2020 | =text(A2,"ddd") | 8 |
| 1/2/2020 | | | 8 |
| 1/3/2020 | | | 8 |
| 1/4/2020 | | | 8 |
| 1/5/2020 | | | 8 |
| 1/6/2020 | | | 0 |
| 1/7/2020 | | | 0 |
| 1/8/2020 | | | 8 |
| ACTIVE | V | 0 |
