Calculate sequential value for future date
 

I have a repeating sequence that begins on a certain date.
For example:
On January 1, 2004, the value is 1.
Jan 2 - 2
Jan 3 - 3
Jan 4 - 4
Jan 5 - 5
Jan 6 - 4
Jan 7 - 3
Jan 8 - 2
Jan 9 - 1
Jan 10 - 2
Jan 11 - 3, and so forth.
I would then like to enter a second date, such as May 20, 2010, and have a
formula tell me what the value will be for that day. Any ideas?
Thanks,
Susan

This will work for any day *after* January 1, 2004:

=CHOOSE(MOD(A1-DATE(2004,1,1),8)+1,1,2,3,4,5,4,3,2)

In article ,
SueDot wrote:

I have a repeating sequence that begins on a certain date.
For example:
On January 1, 2004, the value is 1.
Jan 2 - 2
Jan 3 - 3
Jan 4 - 4
Jan 5 - 5
Jan 6 - 4
Jan 7 - 3
Jan 8 - 2
Jan 9 - 1
Jan 10 - 2
Jan 11 - 3, and so forth.
I would then like to enter a second date, such as May 20, 2010, and have a
formula tell me what the value will be for that day. Any ideas?
Thanks,
Susan

One way,

put this in the adjacent cell to Jan 1 and copy down

=CHOOSE(MOD(ROW(8:8),8)+1,1,2,3,4,5,4,3,2)


Regards,

Peo Sjoblom

"SueDot" wrote:

I have a repeating sequence that begins on a certain date.
For example:
On January 1, 2004, the value is 1.
Jan 2 - 2
Jan 3 - 3
Jan 4 - 4
Jan 5 - 5
Jan 6 - 4
Jan 7 - 3
Jan 8 - 2
Jan 9 - 1
Jan 10 - 2
Jan 11 - 3, and so forth.
I would then like to enter a second date, such as May 20, 2010, and have a
formula tell me what the value will be for that day. Any ideas?
Thanks,
Susan