Good Morning, everyone!
As of right now, I have a spreadsheet that records the number of calls that each employee makes. Columns A-G contain person's name and their daily data, with years of old data. I've also got some information out to the right of these columns. In L1, Today's date, as determined by =TODAY(). L2 contains the Monday that corresponds to the start of the current week, determined by =L1-WEEKDAY(L1,3). L3 contains the corresponding Sunday.
Below that, I have a chart that contains all information from the days between L2 and L3. The formula that I'm using is as follows:
=IFERROR(INDEX(A$2:A$1141,SMALL(IF($A$2:$A$1141>=$L$2,IF($A$2:$A$1141<=$L$3,ROW($A$2:$A$1141)-ROW($A$2)+1)),ROWS(K$7:K7))),"")
Now I have no trouble getting my chart to display all of the data between the dates in L2 and L3, so no biggie there, but I'd like to have it display all of the relevant data for the current pay period based on today's date. Our pay period ends every 14th and 29th, so the number of days per pay period can vary depending on long or short months. For some reason, I can't seem to wrap my head around the way that the formula should look for cells L2 and L3 to produce those dates.
For example, if today is 7/23, I'd like it to return 7/14 in L2, and 7/29 in L3. I want it to return those same dates in L2 and L3 tomorrow and the next day, and I don't want the dates to change until 7/30. Then I want L2 to show 7/30 and I want L3 to show 8/14
Does that make sense?
I've attached my current sheet with dummy data.
Thank you all soooo much!!!
Sample Data.xlsx
Bookmarks