I need help finding a formula or set of formulas that will tell me what I need to know.
I have to take a date range and then calculate how many weeks under each program a person qualified for based on the number of Saturdays.
program dates:
program 1: 04.04.20 - 07.25.20
program 2: 08.01.20 - 09.12.20
program 3: 01.02.21 - 03.13.21
program 4: 03.20.21 - 06.26.21
For example:
if a person was active from 04.13.20 to 02.15.21 then it would say something like
Program1: 15
Program2: 7
Program3: 7
Program4: 0
I have to take a date range and then calculate how many weeks under each program a person qualified for based on the number of Saturdays.
program dates:
program 1: 04.04.20 - 07.25.20
program 2: 08.01.20 - 09.12.20
program 3: 01.02.21 - 03.13.21
program 4: 03.20.21 - 06.26.21
For example:
if a person was active from 04.13.20 to 02.15.21 then it would say something like
Program1: 15
Program2: 7
Program3: 7
Program4: 0
wb example.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | FIRST NAME | LAST NAME | ID# | Wk Start | Wk Stop | Prg1 | Prg2 | Prg3 | Prg4 | Date Entered | ||
2 | 06.13.20 | 06.13.20 | 1 | 05.23.22 | ||||||||
3 | 06.13.20 | 06.20.20 | 2 | 05.31.22 | ||||||||
4 | 06.06.20 | 06.06.20 | 1 | 05.23.22 | ||||||||
5 | 02.06.21 | 02.20.21 | 3 | 05.23.22 | ||||||||
6 | 06.06.20 | 09.05.20 | 7 | 6 | 05.23.22 | |||||||
Sheet1 |