Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the IF function to calculate date | Excel Discussion (Misc queries) | |||
Calculate age as of a given date | Excel Discussion (Misc queries) | |||
Calculate age as of a date certain | Excel Discussion (Misc queries) | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
Calculate date of birth in Excel | Excel Worksheet Functions |