Date cross reference

[philmrs]

I want to do a spreadsheet which uses today() and to cross reference that with a value - for example dates between 4th April 2011 and 10th April 2011 would return a value of "Week 1 Set 1" . The values rotate every 96 days (Week 1 Set 1 to Week 4 Set 3)> Presumably the only way to do this is to have a sheet with the values in and to cross reference the result from today() ?

 

[Peter_SSs]

I suspect that you will need to provide more detail to elicit a useful response.

 

[philmrs]

OK first spreadsheet contains todays date which updates automatically using today(). What I want to do is use this date and cross reference with a table which would return a value eg
...
10 April 2011 Week1 Set 1
11 April 2011 Week2 Set 1
12 April 2011 Week2 Set 1
13 April 2011 Week2 Set 1
14 April 2011 Week2 Set 1
15 April 2011 Week2 Set 1
16 April 2011 Week2 Set 1
17 April 2011 Week2 Set 1
18 April 2011 Week3 Set 1
19 April 2011 Week3 Set 1
...

so that if today is 16th of April it would return a value of Week2 Set 1
and the spreadsheet would display "16 April 2011(Week2 Set 1)"

 

[Peter_SSs]

I've just done this on a single sheet but you should be able to adapt it to a second sheet. Cell D2 would normally contain your =TODAY() formula but I have manually entered a date to suit the data in columns A:B.

If you don't already have/need all the rows like in columns A:B, I've shown an alternative method on the right of the sheet where you just need one row per 'Set', using the first date in the 'Set'.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Week Set		Today			Date	Week Set		Today
2	10 April 2011	Week1 Set 1		16 April 2011	Week2 Set 1		4 April 2011	Week1 Set 1		16 April 2011	Week2 Set 1
3	11 April 2011	Week2 Set 1					11 April 2011	Week2 Set 1
4	12 April 2011	Week2 Set 1					18 April 2011	Week2 Set 1
5	13 April 2011	Week2 Set 1
6	14 April 2011	Week2 Set 1
7	15 April 2011	Week2 Set 1
8	16 April 2011	Week2 Set 1
9	17 April 2011	Week2 Set 1
10	18 April 2011	Week3 Set 1
11	19 April 2011	Week3 Set 1
12
Week Sets