Date Formula with conditions

[Tuzi]

I am trying organize a complicated class schedule for instructors. There are multiple types of classes T1, T2, T3 Each with their own schedule (see below). I have a spreadsheet that lists all of the days of the year across the top starting with column E. In the next row I indicate the Type in column C, and the Start Date in the column D. I need a formula that looks at the Type, looks at Start Date and places an X in the days whene the class is in session.

T1 = Mon-Sat for 16 weeks, every other week (Mon thru Sat On, Sun thru Sun Off, Repeat for 9 weeks)
T2 = Mon-Thu for 20 weeks
T3 = Mon-Fri for 2 weeks

Any Idea how to write this?

 

[sergioMabres]

You can try by using IF AND WEEKDAY to create a formula where both are true day is less than start day plus duration and monday for instance
Or you can write a function that receives start day, type, and current day that is simpler
Sergio

 

[Tuzi]

You can try by using IF AND WEEKDAY to create a formula where both are true day is less than start day plus duration and monday for instance
Or you can write a function that receives start day, type, and current day that is simpler
Sergio

This sounds promising, but how do I write it? Here is what I am trying to write. My columns are now:

Instructor	Type	St Date	31	01	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17
(Drop Down)	(DD)	01/07/13									X	X	X	X				X	X	X	X

<tbody>
</tbody><colgroup><col><col><col><col span="19"></colgroup>

Data Validator to fill Instructor and Type of class
(Type1=M,T,W,Th,F,S On, S,M,T,W,Th,F,S,S off - Repeat for 16 weeks)
(Type2=M,T,W,Th on, F,S,S Off - Repeat for 20 weeks)
When you type in the Start Date, it places an X in the column for the days when class meets, and leaves the remainin days clear. The days of the year go to the right forever.

I will need to create another sheet that displays the info in a calendar format so it can be reviewed easier.

 

[sergioMabres]

Hi Tuzi,
As I commented in my last post you have two ways formula or function, usually formulas are long and easy to write, while function are shorter but harder to write, in this case for Type 1 and 2 the formula could be:<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))

</d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)>

=IF($B3="Type 1",IF(D$1<$C3,"",
IF($C3+16*7 < D$1 , "" ,
IF(AND(WEEKDAY(D$1,2)<7,
ISEVEN(INT((D$1-2)/7)-INT(($C3-2)/7))),"X",""))),
IF($B3="Type 2",IF(D$1<$C3,"",
IF($C3+20*7 < D$1 , "" ,
IF(WEEKDAY(D$1,2)<5,"X",""))),""))

<d$1,\"\",
<d$1,"",
<d$1,"",
<d$1,"",

</d$1,"",
</d$1,"",
</d$1,"",
</d$1,\"\",
<d$1,\"\",
<d$1,"",
<d$1,"",
<d$1,"",
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))[="" quote]
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))[="" quote]
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))
<d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2) 7)-int(($c3-2)="" 7))),"x",""))),if($b3="Type 2" ,if(d$1<$c3,"",if($c3+20*7<d$1,"",if(weekday(d$1,2)<5,"x",""))),""))

You can test the formula in the test sheet you can download from https://dl.dropbox.com/u/23094164/Tuzi1.xlsx
Remember to change date format to your country's date format
Sergio</d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",if(and(weekday(d$1,2)<7,iseven(int((d$1-2)></d$1,"",
</d$1,"",
</d$1,"",
</d$1,\"\",