I am stuck, I have an excel sheet that has date formulas to automate the payperiod date data, providing the day of week and the date of the month. My company's work week starts with a Sun and ends with a Sat. I need a formula that will look through the column that shows the day of week and pull the data for the first Sun and the next 6 days, pulling 2 columns of data for Rate 1 and Rate 2 Hours. I then also need the same type of formula but for the second Sun from the Day Column, then the next 6 days after that. I need for this to automatically update when ever we change the date data, this is why I am doing the formula this way. Please note that the day column Cell B15 is the result of the =DATE($C$8,MATCH($D$8,Data!$B$2:$B$13,0),Example!$E$8) formula. And the rest of the Day Column is the result of =B15+1. I have tried various formulas from VLookup and index, but I am just not getting it for some reason. The Day Column B15:B32 is formatted as custom ddd and I also made sure that the J column days of the week (that I was using as the search variable) were also formatted with the same.
This is to help me in determining the Weighted Overtime Rates for a Split Rate Semi Monthly employee. I know how to do the calculations, but am trying to automate a spreadsheet for the office staff to be able to use to with minimal calculations on their part.
Thank you for any and all help,
Star
Capture.JPG
Bookmarks