I need a formula to check if today is a workday between two dates

[joeljoel]

I have a spreadsheet set up with rows containing weekdays only and the following columns
A - full date, including day of week
C - Year
D - Month
E - Day
F - day of week (mon, tues, etc)
G - weekday number (formula calculates whether it day is the 1st, 2nd, 3rd etc) weekday of the month.

So now I need to add several similar columns all of which are testing if the date, say row5, is between 3-4 time periods. For example, is the date in A5 between the 10th weekday in February and 2nd weekday in March or between the 17th weekday of June and the 7th weekday of July or between the 18th weekday of November and the 6th weekday of December.

I hope I explained this well enough. Thanks so much for your help!!

- Joel

 

[BeingYogi]

can u provide the data sheet sample to understand it better.

 

[AlphaFrog]

<br /><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 /><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><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Date</td><td style="font-weight: bold;text-align: center;;">Year</td><td style="font-weight: bold;text-align: center;;">Month</td><td style="font-weight: bold;text-align: center;;">Day</td><td style="font-weight: bold;text-align: center;;">Day of week</td><td style="font-weight: bold;text-align: center;;">Workday</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Tuesday, June 28, 2011</td><td style="text-align: center;;">2011</td><td style="text-align: center;;">June</td><td style="text-align: center;;">28</td><td style="text-align: center;;">Tuesday</td><td style="text-align: center;;">20</td></tr></tbody></table><br /><br /><table 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">B5</th><td style="text-align:left">=YEAR(<font color="Blue">A5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=TEXT(<font color="Blue">A5,"mmmm"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=DAY(<font color="Blue">A5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=TEXT(<font color="Blue">A5,"dddd"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=NETWORKDAYS(<font color="Blue">A5-D5+1,A5</font>)</td></tr></tbody></table></td></tr></table><br />

<br /><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 /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;background-color: #00FF00;;">Start</td><td style="font-weight: bold;text-align: center;background-color: #00FF00;;"></td><td style="font-weight: bold;text-align: center;background-color: #00FF00;;"></td><td style="font-weight: bold;text-align: center;background-color: #00FFFF;;">End</td><td style="text-align: center;background-color: #00FFFF;;"></td><td style="text-align: center;background-color: #00FFFF;;"></td><td style="font-weight: bold;text-align: center;;">A5 Is Between</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Month</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Workday</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Date</td><td style="font-weight: bold;text-align: center;background-color: #99CCFF;;">Month</td><td style="font-weight: bold;text-align: center;background-color: #99CCFF;;">Workday</td><td style="font-weight: bold;text-align: center;background-color: #99CCFF;;">Date</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #CCFFCC;;">February</td><td style="text-align: center;background-color: #CCFFCC;;">10</td><td style="text-align: center;background-color: #CCFFCC;;">2/14/2011</td><td style="text-align: center;background-color: #99CCFF;;">March</td><td style="text-align: center;background-color: #99CCFF;;">2</td><td style="text-align: center;background-color: #99CCFF;;">3/2/2011</td><td style="text-align: center;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #CCFFCC;;">June</td><td style="text-align: center;background-color: #CCFFCC;;">17</td><td style="text-align: center;background-color: #CCFFCC;;">6/23/2011</td><td style="text-align: center;background-color: #99CCFF;;">July</td><td style="text-align: center;background-color: #99CCFF;;">7</td><td style="text-align: center;background-color: #99CCFF;;">7/11/2011</td><td style="text-align: center;;">TRUE</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #CCFFCC;;">November</td><td style="text-align: center;background-color: #CCFFCC;;">18</td><td style="text-align: center;background-color: #CCFFCC;;">11/24/2011</td><td style="text-align: center;background-color: #99CCFF;;">December</td><td style="text-align: center;background-color: #99CCFF;;">6</td><td style="text-align: center;background-color: #99CCFF;;">12/8/2011</td><td style="text-align: center;;">FALSE</td></tr></tbody></table><br /><br /><table 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">M5</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">K5 & " 1, " &$B$5</font>)-1,L5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N5</th><td style="text-align:left">=AND(<font color="Blue">$A$5>=J5, $A$5<=M5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M6</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">K6 & " 1, " &$B$5</font>)-1,L6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N6</th><td style="text-align:left">=AND(<font color="Blue">$A$5>=J6, $A$5<=M6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">M7</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">K7 & " 1, " &$B$5</font>)-1,L7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">N7</th><td style="text-align:left">=AND(<font color="Blue">$A$5>=J7, $A$5<=M7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J5</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">H5 & " 1, " &$B$5</font>)-1,I5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J6</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">H6 & " 1, " &$B$5</font>)-1,I6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J7</th><td style="text-align:left">=WORKDAY(<font color="Blue">DATEVALUE(<font color="Red">H7 & " 1, " &$B$5</font>)-1,I7</font>)</td></tr></tbody></table></td></tr></table><br />

 

[joeljoel]

Thanks Alpha Frog. But is there a way to do this without the extra columns. I have 50 or so time periods to test and 7 new columns for each one will be too cumbersome. In addition, the example I gave would be for one column test. For example, if today's date is between any of the three time periods, enter a 1 in this column.

Does that make sense?

 

[AlphaFrog]

Thanks Alpha Frog. But is there a way to do this without the extra columns. I have 50 or so time periods to test and 7 new columns for each one will be too cumbersome. In addition, the example I gave would be for one column test. For example, if today's date is between any of the three time periods, enter a 1 in this column.

Does that make sense?

Are the three "between time period criteria" always the same for each of the 50 or so dates you want to test?

 

[joeljoel]

None of the time periods are the same. There will be some overlap, but each columns is testing different things and seeking dates within different time periods. Some columns will ask if the date is within 3 time periods, another asks if it is within 12 or 13 time periods. A couple need a value of 1 if within 5-6 timer periods and value of -1 if within 5-6 different time periods, and 0 if within none.

 

[AlphaFrog]

I understand the result you want but don't understand how your data is laid out. You want to test the 50 dates in column A each with unique criteria? Where are the criteria located relative to each of the 50 dates?

Can you post a short sample data set with your criteria for each date and the expected result? I'm sure a formula could be developed. Note the links in my signature below.

Are you trying to determine if a workday falls on a weekday holiday?

 

[joeljoel]

OK. How does one upload a excel spreadsheet? I thought there used to be a paperclip attach button.