Work in which office over last 30 and 60 days

[tracer773]

Thank you for your help up front. Not very excel literate but trying to update a product.

I need to be able to track how many days a student worked in office A, B, and C over the last 30 days and 60 days. So in the first column I have everyones name and then the columns after that are dates. So if employee 1 worked in office B that day, the letter B would be in that cell. We manually update the excel product everyday and I need a formula that will take today's date, count back 30 or 60 days, and give me 6 different answers per student. (30 and 60 day count for office A/B/C)

Hope that is clear enough. Again, thanks for the help.

 

[Akashwani]

Hi and welcome to MrExcel.

If I understand you correctly and I have my formulas right.
Based on the following sample data, would you expect the results displayed to be correct?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Student	10/01/2013	11/02/2013	12/02/2013	13/02/2013	14/02/2013	01/03/2013	02/03/2013	03/03/2013	04/03/2013
2	Bill	A	B	C	B	C	A	B	C	C
3	Ben	A	A	B	C	B	C	A	B	C
4	Bob	A	B	A	B	C	B	C	A	B
5	Tim	A	B	B	A	B	C	B	C	A
6	Tom	A	B	C	C	B	B	C	B	C
7	Tod	C	A	B	C	C	B	B	C	B
8	Ted	B	C	A	B	C	C	A	B	C
9
10
11			04/03/2013	30 Days		03/04/2013		60 Days	02/02/2013
12		Student	A	B	C		A	B	C
13		Bill	0	0	1		1	3	4
14		Ben	0	0	1		2	3	3
15		Bob	0	1	0		2	4	2
16		Tim	1	0	0		2	4	2
17		Tom	0	0	1		0	4	4
18		Tod	0	1	0		1	4	3
19		Ted	0	0	1		2	2	4
20
Sheet10

The formula in C13 needs to be copied across and down.
The formula in G13 needs to be copied across and down.

Obviously you will need to change the cell reference to suit your layout.

I hope that helps.

Ak