DashboardNovice
Member
In the attached file, the raw data is on the first tab. The output that I am trying to get is on the second tab.
On the first tab here is what I have:
1. Columns A and B list timesheet week ending dates (a Sunday) that a person worked.
2. Column H shows all possible week ending dates for the contract year.
3. K2:M6 shows each person's start date and termination date. This shows the first and last days they should have hours on. In column B, I am working with week ending dates (on Sunday) not any individual dates during the week. So if person started or terminated in the middle of the week, their time for that week would be reported on that Sunday, i.e. if terminated on a Wednesday, "round up" to the next Sunday. That weekending date should appear in column B.
4. Based on when each person started and ended, I want to take their week ending dates in column B and check them against column H. If they are missing a week, I need to know which week they are missing (see the Output tab).
Example 1: Greg Smith was terminated on Wednesday, 07.16.2014, his last timesheet should be for week ending 07.20.2014. But this does not appear. Maybe he didn't fill out his timesheet for the last few days of work. Therefore, on the Output tab, Greg Smith has 07.20.2014 in column B.
Example 2: Ken Williams started on 10.23.2013, so his first timesheet should be for week ending 10.27.2013, but he is missing 10.27.2013, so that appears the out put tab.
Example 3: Larry Cardenas does not have a timesheet for 03.16.2014. He should because that date appears during his employment dates. So 03.16.2014 shows up for Larry Cardenas on the Output tab.
I would greatly appreciate any help.
On the first tab here is what I have:
1. Columns A and B list timesheet week ending dates (a Sunday) that a person worked.
2. Column H shows all possible week ending dates for the contract year.
3. K2:M6 shows each person's start date and termination date. This shows the first and last days they should have hours on. In column B, I am working with week ending dates (on Sunday) not any individual dates during the week. So if person started or terminated in the middle of the week, their time for that week would be reported on that Sunday, i.e. if terminated on a Wednesday, "round up" to the next Sunday. That weekending date should appear in column B.
4. Based on when each person started and ended, I want to take their week ending dates in column B and check them against column H. If they are missing a week, I need to know which week they are missing (see the Output tab).
Example 1: Greg Smith was terminated on Wednesday, 07.16.2014, his last timesheet should be for week ending 07.20.2014. But this does not appear. Maybe he didn't fill out his timesheet for the last few days of work. Therefore, on the Output tab, Greg Smith has 07.20.2014 in column B.
Example 2: Ken Williams started on 10.23.2013, so his first timesheet should be for week ending 10.27.2013, but he is missing 10.27.2013, so that appears the out put tab.
Example 3: Larry Cardenas does not have a timesheet for 03.16.2014. He should because that date appears during his employment dates. So 03.16.2014 shows up for Larry Cardenas on the Output tab.
I would greatly appreciate any help.
