calculating a pattern

J

jimo

Using the following pattern:

DATE = ANSWER
---------- ------
12/16/2007 = 1A
12/17/2007 = 2A
12/18/2007 = 3A
12/19/2007 = 1B
12/20/2007 = 2B
12/21/2007 = 3B
12/22/2007 = 1A
12/23/2007 = 2A
12/24/2007 = 3A
12/25/2007 = 1B
12/26/2007 = 2B
12/27/2007 = 3B
12/28/2007 = 1A
12/29/2007 = 2A
12/30/2007 = 3A

how can I calculate the "ANSWER" when "DATE" = January 27, 2008?

DATE = ANSWER
---------- ------
1/27/2008 = ?


or any other date for that matter?


Thank you.
Jim O:)
 
M

Max

One thought

Assuming the first 6 lines below
are within A2:B7, viz:
12/16/2007 = 1A
12/17/2007 = 2A
12/18/2007 = 3A
12/19/2007 = 1B
12/20/2007 = 2B
12/21/2007 = 3B
Click to expand...

Select A2:B7, fill down to propagate the series for future dates as far as
required. Then just use a simple vlookup on the input date,
eg with input date in D2 down: 12/21/2007
Put in E2: =IF(D2="","",VLOOKUP(D2,A:B,2,0))
Copy down
 
R

RagDyer

Enter the date to find in A1, then try this:

=CHOOSE(MOD(A1,6)+1,"1A","2A","3A","1B","2B","3B")
 
J

jimo

Looks like you hit it on the head, RagDyer!
This will help create a work shift calculator for firefighters.
I'm going to try and understand the "CHOOSE" and "MOD" functions now.
 
R

RagDyer

Appreciate the feed-back.

Actually, both the Choose() and Mod() functions are pretty straight forward,
as you can find out by looking in the Help files.

It's really just the mathematical adjustment to Mod to make it return the
number series (1 to 6) that you need to feed the Choose function.
 
Top