Please Help with date formula

[Eraque]

I work shift work in a firehouse that has 4 shifts
labeled "A shift", "B Shift", "C Shift", and "D Shift".
If January 1, 1900 is a "C Shift", I need a formula to
calculate the Shift for any current date. Lets say I
enter 2/14/2004 in one cell, I would like the computer to
calculate "A Shift" in the adjacent cell. Any help or
clues would be greatly appreciated. TIA!

Eraque

 

[Frank Kabel]

hi
one way would be
=CHOOSE(MOD(A1,4),"A-Shift","B-Shift","C-Shift","D-Shift")
Where A1 stores your date (If I understood your example correctly)

 

[Jason Morin]

You haven't given enough information to help us understand
exactly when a shift works. For example, does each shift
work 1 whole day and then the next shift comes in (Mon =
A, Tues = B, Wed. = C, Thurs. = D, Fri. = A, etc.)? What
is the sequence or pattern?

HTH
Jason
Atlanta, GA

 

[Eraque]

I Apologize. We do work 24 hour shifts. Today,
2/14/2004 is an "A Shift", Tomorrow, 2/15/2004 is a "B
Shift", etc.... I tried the Mod function as recommended
in the other reply from Frank without success. Thanks
for any and all help.

Eraque

 

[Eraque]

Frank,
Thanks for your help, but the example isn't working.
It keeps calculating a #VALUE error on every day except
today. Strange. ANy other suggestions. Danke.

Eraque

 

[Ken Wright]

I think you'll find Frank simply missed the +1 from his formula, eg:-

=CHOOSE(MOD(A1,4)+1,"A-Shift","B-Shift","C-Shift","D-Shift")

The CHOOSE function is looking for a 1,2,3,4 out of the MOD function. Without
the +1 it is seeing a 0,1,2,3 and it can't handle 0.

 

[Frank Kabel]

Sorry
my fault. Try
=CHOOSE(MOD(A1,4)+1,"A-Shift","B-Shift","C-Shift","D- Shift")

 

[Jason Morin]

=CHAR(IF(MOD(A1,4)=3,65,MOD(A1,4)+66))&" Shift"

HTH
Jason
Atlanta, GA