generate a set of cells with 4 weekdays in them

[magicmusic]

I teach college music - and need to consistently make and give students a rehearsal plan for a choir which meets 4 of the 5 days (M, W, Th, F) each week. Each plan is usually different lengths of time - sometimes it's a couple weeks, sometimes a whole quarter (10 weeks).

I currently type each date manually for each separate plan - but would like to automate the process.

I would like to be able to generate a set of vertical cells which would contain x amount of days - where x is the amount of class meeting days between date entry 1 and date entry 2.

I would like each cell formatted with mo/day (day first letter),

for example 9/6 (T)

I have been reading the Mr Excel archives for date ideas, and have seen formulas to return #of days in month, 1st Monday of a Month, how many calendar days between dates, etc. I also have been introduced to the workday function - which seems pretty helpful in this case.

I've messed with Macros and VB - though I'm the kind of "programmer" who, left to my own devices, usually needs 10 lines of code where two would do if generated by an expert.

My idea is for a sheet that looks like this:
A2 - begin date
B2 - end date
C2 - amount of class days between A1&B1 (4/week, M, W, Th, F)

A4, A5, A6 . . . (through the number returned in C1)



Thus - the result look something like below:

*********************************

BeginDate | End Date | ClassMeetings
9/15/2011 | 11/4/2011 | 30

9/15 (Th)
9/16 (F)
9/19 (M)
9/21 (W)
|
|
11/4 (F)

**********************************
(no Tuesday meeting)

Again, this list twould be a different length each time, depending upon begin and end date . . .

Thanks in advance for getting me started in the right direction!

Gary

 

[markmzz]

Gary,

First, I will try to explain the part below of the formula.

CHOOSE(WEEKDAY(MAX(A$3:A3),2),2,,1,1,3))))

If the weekday of the previous day is 1 - Mon, 2 - Tue , 3 - Wed, 4 - Thu, 5 - Fri, then the CHOOSE function choose 2, , 1, 1 and 3 (the number of days to add to the previous day to get the present day). Note that for the Tue weekday I don't add nothing (I only have Mon, Wed, Thu and Fri in the column A).

Now, the formulas for the class meetings counted:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">   Begin Date   </td><td style="font-weight: bold;text-align: center;;">     End Date     </td><td style="font-weight: bold;text-align: center;;">ClassMeetings</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">9/15/2011</td><td style="text-align: center;;">11/4/2011</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">9/15 Thu</td><td style=";">9/15 (Th)-1</td><td style=";">9/15 (Th)-1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">9/16 Fri</td><td style=";">9/16 (F)-2</td><td style=";">9/16 (F)-1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">9/19 Mon</td><td style=";">9/19 (M)-3</td><td style=";">9/19 (M)-1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">9/21 Wed</td><td style=";">9/21 (W)-4</td><td style=";">9/21 (W)-1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">9/22 Thu</td><td style=";">9/22 (Th)-5</td><td style=";">9/22 (Th)-2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">9/23 Fri</td><td style=";">9/23 (F)-6</td><td style=";">9/23 (F)-2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">9/26 Mon</td><td style=";">9/26 (M)-7</td><td style=";">9/26 (M)-2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">9/28 Wed</td><td style=";">9/28 (W)-8</td><td style=";">9/28 (W)-2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">9/29 Thu</td><td style=";">9/29 (Th)-9</td><td style=";">9/29 (Th)-3</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">9/30 Fri</td><td style=";">9/30 (F)-10</td><td style=";">9/30 (F)-3</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">10/3 Mon</td><td style=";">10/3 (M)-11</td><td style=";">10/3 (M)-3</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">10/5 Wed</td><td style=";">10/5 (W)-12</td><td style=";">10/5 (W)-3</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">10/6 Thu</td><td style=";">10/6 (Th)-13</td><td style=";">10/6 (Th)-4</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">10/7 Fri</td><td style=";">10/7 (F)-14</td><td style=";">10/7 (F)-4</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">10/10 Mon</td><td style=";">10/10 (M)-15</td><td style=";">10/10 (M)-4</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">10/12 Wed</td><td style=";">10/12 (W)-16</td><td style=";">10/12 (W)-4</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">10/13 Thu</td><td style=";">10/13 (Th)-17</td><td style=";">10/13 (Th)-5</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">10/14 Fri</td><td style=";">10/14 (F)-18</td><td style=";">10/14 (F)-5</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">10/17 Mon</td><td style=";">10/17 (M)-19</td><td style=";">10/17 (M)-5</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">10/19 Wed</td><td style=";">10/19 (W)-20</td><td style=";">10/19 (W)-5</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">10/20 Thu</td><td style=";">10/20 (Th)-21</td><td style=";">10/20 (Th)-6</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">10/21 Fri</td><td style=";">10/21 (F)-22</td><td style=";">10/21 (F)-6</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">10/24 Mon</td><td style=";">10/24 (M)-23</td><td style=";">10/24 (M)-6</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">10/26 Wed</td><td style=";">10/26 (W)-24</td><td style=";">10/26 (W)-6</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">10/27 Thu</td><td style=";">10/27 (Th)-25</td><td style=";">10/27 (Th)-7</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">10/28 Fri</td><td style=";">10/28 (F)-26</td><td style=";">10/28 (F)-7</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">10/31 Mon</td><td style=";">10/31 (M)-27</td><td style=";">10/31 (M)-7</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: right;;">11/2 Wed</td><td style=";">11/2 (W)-28</td><td style=";">11/2 (W)-7</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;">11/3 Thu</td><td style=";">11/3 (Th)-29</td><td style=";">11/3 (Th)-8</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;">11/4 Fri</td><td style=";">11/4 (F)-30</td><td style=";">11/4 (F)-8</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=COUNT(<font color="Blue">A:A</font>)-1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">A$4:A4</font>)=1,A$2,IF(<font color="Red">MAX(<font color="Green">A$3:A3</font>)=B$2,"",IF(<font color="Green">WEEKDAY(<font color="Purple">MAX(<font color="Teal">A3</font>),2</font>)=5,"",MAX(<font color="Purple">A$3:A3</font>)+CHOOSE(<font color="Purple">WEEKDAY(<font color="Teal">MAX(<font color="#FF00FF">A$3:A3</font>),2</font>),2,,1,1,3</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">A4="","",TEXT(<font color="Red">A4,"m/d"</font>)&" ("&CHOOSE(<font color="Red">WEEKDAY(<font color="Green">A4,2</font>),"M",,"W","Th","F"</font>)&")-"&COUNT(<font color="Red">A$4:A4</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">{=IF(<font color="Blue">A4="","",TEXT(<font color="Red">A4,"m/d"</font>)&" ("&CHOOSE(<font color="Red">WEEKDAY(<font color="Green">A4,2</font>),"M",,"W","Th","F"</font>)&")-"&1+SUM(<font color="Red">--IF(<font color="Green">ISNUMBER(<font color="Purple">A$3:A3</font>),WEEKDAY(<font color="Purple">A$3:A3,2</font>)=WEEKDAY(<font color="Purple">A4,2</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
I hope this help you to understand.

Markmzz

 

[magicmusic]

Thanks, Markmzz - I actually understand - and because of that was able to modify the A4 & B4 formulas to also generate date lists for just M, W, F and for Tu, Th - since I also need those.

I'm not sure why you gave me column C - which looks like it counts what week of class it is? Even so, that's a pretty cool addition.

 

[markmzz]

Thanks, Markmzz - I actually understand - and because of that was able to modify the A4 & B4 formulas to also generate date lists for just M, W, F and for Tu, Th - since I also need those.

I'm not sure why you gave me column C - which looks like it counts what week of class it is? Even so, that's a pretty cool addition.

Magicmusicm,

I was in doubt if you wanted a column B or C result.

And thanks for the feedback.

Markmzz

 

[magicmusic]

Thanks for the all the tips – see below for my result: Note the class meeting number (thanks Markmzz) in column B.

Although the original request was for a M, W, Th, F class – I also teach Th, Th and M, W, F classes – which also need date lists - so I'm showing here a Mon, Wed, Fri list. Since I need these types of lists multiple times a quarter, this will save a lot of time.

#VALUE!

#VALUE!

#VALUE!

#VALUE!

#VALUE!

Excel 2007

Cell Formulas
Range	Formula
A4	=IF(ROWS(A$4:A4)=1,A$2,IF(MAX(A$3:A3)=B$2,"",IF(WEEKDAY(MAX(A3),2)=5,"",MAX(A$3:A3)+CHOOSE(WEEKDAY(MAX(A$3:A3),2),2,,2,,3))))
B4	=IF(A4="","","("&COUNT(A$4:A4)&")" )&IF(A4="","",TEXT(A4," m/d")&" "&CHOOSE(WEEKDAY(A4,2),"M",,"W",,"F"))
C2	=COUNT(A:A)-1

 

[markmzz]

Gary,

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">   Begin Date   </td><td style="font-weight: bold;text-align: center;;">     End Date     </td><td style="font-weight: bold;text-align: center;;">ClassMeetings</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Holidays</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">9/16/2011</td><td style="text-align: center;;">11/4/2011</td><td style="text-align: center;;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;">10/12 Wed</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">10/21 Fri</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">9/16 Fri</td><td style="text-align: center;;">(1) 9/16 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">9/26 Mon</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">9/19 Mon</td><td style="text-align: center;;">(2) 9/19 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">9/21 Wed</td><td style="text-align: center;;">(3) 9/21 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">9/23 Fri</td><td style="text-align: center;;">(4) 9/23 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9/26 Mon</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">9/28 Wed</td><td style="text-align: center;;">(5) 9/28 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">9/30 Fri</td><td style="text-align: center;;">(6) 9/30 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">10/3 Mon</td><td style="text-align: center;;">(7) 10/3 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">10/5 Wed</td><td style="text-align: center;;">(8) 10/5 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">10/7 Fri</td><td style="text-align: center;;">(9) 10/7 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">10/10 Mon</td><td style="text-align: center;;">(10) 10/10 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">10/12 Wed</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">10/14 Fri</td><td style="text-align: center;;">(11) 10/14 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">10/17 Mon</td><td style="text-align: center;;">(12) 10/17 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">10/19 Wed</td><td style="text-align: center;;">(13) 10/19 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">10/21 Fri</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">10/24 Mon</td><td style="text-align: center;;">(14) 10/24 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">10/26 Wed</td><td style="text-align: center;;">(15) 10/26 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">10/28 Fri</td><td style="text-align: center;;">(16) 10/28 F</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">10/31 Mon</td><td style="text-align: center;;">(17) 10/31 M</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">11/2 Wed</td><td style="text-align: center;;">(18) 11/2 W</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">11/4 Fri</td><td style="text-align: center;;">(19) 11/4 F</td><td style="text-align: center;;"></td><td style="text-align: center;;">*****</td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=COUNT(<font color="Blue">A:A</font>)-1-SUMPRODUCT(<font color="Blue">--ISNUMBER(<font color="Red">MATCH(<font color="Green">A:A,holidays,0</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A4</th><td style="text-align:left">=IF(<font color="Blue">ROWS(<font color="Red">A$4:A4</font>)=1,A$2,IF(<font color="Red">MAX(<font color="Green">A$3:A3</font>)=B$2,"",IF(<font color="Green">WEEKDAY(<font color="Purple">MAX(<font color="Teal">A3</font>),2</font>)=5,"",MAX(<font color="Purple">A$3:A3</font>)+CHOOSE(<font color="Purple">WEEKDAY(<font color="Teal">MAX(<font color="#FF00FF">A$3:A3</font>),2</font>),2,,2,,3</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A4="",ISNUMBER(<font color="Green">MATCH(<font color="Purple">A4,holidays,0</font>)</font>)</font>),"","("&1+SUMPRODUCT(<font color="Red">--(<font color="Green">B$3:B3<>""</font>)</font>)&") "&TEXT(<font color="Red">A4,"m/d "</font>)&CHOOSE(<font color="Red">WEEKDAY(<font color="Green">A4,2</font>),"M",,"W",,"F"</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">holidays</th><td style="text-align:left">=Sheet5!$E$2:$E$4</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[magicmusic]

Markmzz -

Worked great! Thanks!!

 

[markmzz]

Markmzz -

Worked great! Thanks!!

Gary,

Thank you for the feedback.

Markmzz