Darren_workforce
Board Regular
- Joined
- Oct 13, 2022
- Messages
- 145
- Office Version
- 365
- Platform
- Windows
I have a spreadsheet, on which there are 5 columns. Row 1 has the day of the week and the remaining rows have start times for my team. I would like to reference these in another workbook so I can determine the time difference between when they punched in versus when their shift starts. (I don't have access to the payroll software due to HR restrictions).
Based on the actual day of the week I run this, I'd like to have a formula that locates the respective column and then generates the corresponding start time within that column. I anticipate it would require an Index/Match formula but can that be done in combination with a weekday lookup? Below is the formula I used that would pull Thursday's data but I was hoping to setup something within a macro that could automatically pull without me needing to update the column index number each day of the week. TYIA
Based on the actual day of the week I run this, I'd like to have a formula that locates the respective column and then generates the corresponding start time within that column. I anticipate it would require an Index/Match formula but can that be done in combination with a weekday lookup? Below is the formula I used that would pull Thursday's data but I was hoping to setup something within a macro that could automatically pull without me needing to update the column index number each day of the week. TYIA
Excel Formula:
=VLOOKUP(A2,'[Start Times.xlsx]Sheet1'!$B:$F,4,FALSE)
Monday | Tuesday | Wednesday | Thursday | Friday |
9:30 | 9:30 | 10:00 | 10:30 | 9:30 |
9:30 | 9:30 | 12:30 | 9:30 | 10:00 |
9:30 | 9:30 | 9:30 | 9:30 | 9:30 |
11:00 | 9:00 | 8:00 | 8:00 | 8:00 |
8:00 | 8:00 | 8:00 | 9:00 | 8:00 |
8:30 | 9:30 | 8:30 | 8:30 | 11:30 |
10:30 | 9:30 | 9:30 | 9:30 | 8:30 |
9:30 | 9:30 | 9:30 | 9:30 | 9:30 |
9:00 | 9:00 | 9:00 | 9:00 | 9:00 |