Please Help with date formula

  • Thread starter Thread starter Eraque
  • Start date Start date
E

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
 
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)
 
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
 
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
 
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
 
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.
 
Back
Top