# Date Formula with conditions

#### Tuzi

##### New Member
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?

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### sergioMabres

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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,\"\",

Replies
4
Views
117
Replies
6
Views
141
Replies
5
Views
181
Replies
9
Views
378
Replies
2
Views
206

### Forum statistics

1,195,683
Messages
6,011,137
Members
441,587
Latest member
kbsgiri09 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back