While I am sure this formula can be made much trimmer, here is a modified
(read that as **highly** patched) formula which I believe corrects the
problems you pointed out and one problem that you didn't mention (every
other week, the alternate code after midnight was incorrect). The formula is
ugly, but I am pretty sure it works.

=IF(A1="","",IF(AND(MOD(B1,1)TIME(7,0,0),MOD(B1,1 )<=TIME(19,0,0)),IF(WEEKDAY(A1)<4,"A",IF(WEEKDAY(A 1)4,"C",IF(MOD(ROUNDUP(A1/7,0),2),"<A","<C"))),IF(AND(OR(WEEKDAY(A1)<4,AND (WEEKDAY(A1)=4,B1<TIME(7,1,0))),NOT(AND(WEEKDAY(A1 )=1,B1<TIME(7,1,0)))),"E",IF(AND(OR(WEEKDAY(A1)<7, AND(WEEKDAY(A1)=7,B1<TIME(7,1,0))),NOT(AND(WEEKDAY (A1)=1,B1<TIME(7,1,0)))),"F",IF(MOD(ROUNDUP((A1-(WEEKDAY(A1)=1))/7,0),2),"<<E","<<F")))))

As before, once you decide it is working correctly, simply remove the angle
bracket.

I have an idea on a method to trim this down considerably. If this idea pans
out, I will post the revised formula here... so keep checking back into this
thread for, say, the next week to see if I succeeded or not.

Rick


"Dan" wrote in message
...
Rick- I sure appreciate your help on this. The days alternate perfectly as
you have it, the only issue is on Tuesday night/Wednesday morning at 00:00
the shift switches from E to F (should stay E thru 07:00). Same thing
happens
on Friday night/Saturday morning at 00:00,shift switches from F to E
(Should
stay F shift thru 07:00). Each shift is 12 hr shift from 19:00 to 07:00.

Thanks again

"Rick Rothstein (MVP - VB)" wrote:

Okay, I think the only (major) thing "wrong" with my original formula is
the
code for the alternating Saturdays. Try this...

=IF(A2="","",IF(AND(MOD(B2,1)TIME(7,0,0),MOD(B2,1 )<=TIME(19,0,0)),IF(WEEKDAY(A2)<4,"A",IF(WEEKDAY(A 2)4,"C",IF(MOD(ROUNDUP(A2/7,0),2),"<A","<C"))),IF(WEEKDAY(A2)<4,"E",IF(WEE KDAY(A2)<7,"F",IF(MOD(ROUNDUP(A2/7,0),2),"<<E","<<F")))))

Remember, though, for the alternating Wednesdays, you may want the <A
and
<C codes reversed depending on which week <A starts on. Same for the
alternating Saturdays... you may want the <<E and <<F codes reversed
depending on which week <<E starts on. As I said, you can identify
these
two conditionals easy enough by the single angle brackets and double
angle
brackets. If the alternating codes for either or both of these are coming
up
reversed from what you want, simply swap the <A and <C with each other
and/or the <<E and <<F with each other. After you have them in the
right
order, just remove all the angle brackets from the first equation and
copy
that down as far as you like.

Rick


"Dan" wrote in message
...
Sorry about the confusion. Basically...day shifts (A/C) alternate
working
Wednesdays (07:00 to 19:00), and night shifts alternate working
Saturday
nights (E/F) 19:00 to 07:00.

"Rick Rothstein (MVP - VB)" wrote:

Every Sunday/Monday/Tuesday and every other Wednesday 07:00 to 19:00
is
A-shift
Every other Wednesday/Thursday/Friday/Saturday 07:00 to 19:00 is
C-shift
Every other Saturday/Sunday/Monday/Tuesday nights from 19:00 to
07:00
is
E-shift
Every Wednesday/Thursday/Friday and every other Saturday 19:00 to
07:00
is
F-Shift

This isn't what you said in your first post, is it??? You are now
showing
days other than Wednesday and Saturday alternating... is that correct?
Also,
your first chart showed Saturdays alternating between A and C, not E
and
F.
Can you lay out another chart like the first one showing exactly what
you
want? Please check what you post carefully... the formulas we develop
are
hand-crafted to the information you ask for... they are not always
easily
changed around to account for typos.

Rick