Count number of occurances for each full 7 day week

kawelsh

New Member
Joined
May 24, 2012
Messages
1
Hi - I am trying to figure out a formula in Excel 2007 that will count how many meetings take place in any given full 7 day week ongoing (but always Sunday-Saturday). Column A has the meeting start date, column B has the meeting end date. The formula should highlight those meetings that fall within a regular week.

Example

Mtg Start Mtg End Date
<TABLE style="WIDTH: 143pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=190 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20 width=108 x:num="40945">2/6/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=82 x:num="40946">2/7/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20 x:num="40949">2/10/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 x:num="40950">2/11/2012</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=23 width=108 x:num="40956">2/17/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 width=82 x:num="40957">2/18/2012</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=23 x:num="40964">2/25/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 x:num="40964">2/25/2012</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=23 width=108 x:num="40604">3/2/2011</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 width=82 x:num="40605">3/3/2011</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=23 x:num="40975">3/7/2012</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 x:num="40976">3/8/2012</TD></TR></TBODY></TABLE>

Ideally I'd like to know which of these meetings falls within the Sunday - Saturday time frame for any year, this year and in the future. If you see the above for 2012 the weeks were Sun Feb 5-Sat Feb 11, Sun Feb 12 - Sat Feb 18.. I just need to know if the START date of the meeting falls within the given week. Is this a possible formula that Excel can provide?

The column with this answer should look something like this:

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=154 x:str><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; WIDTH: 116pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20 width=154>Sun 2/5 - Sat 2/11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>Sun 2/5 - Sat 2/11</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #339966; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=23>Sun 2/12 - Sat 2/18</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; WIDTH: 116pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl72 height=23 width=154>Sun 2/19 - Sat 2/25</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69 height=23>Sun 2/26 - Sat 3/3</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; WIDTH: 116pt; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl73 height=23 width=154> </TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #33cccc; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=40>Sun 3/4 - Sat 3/10</TD></TR><TR style="HEIGHT: 17.25pt; mso-height-source: userset" height=23><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #33cccc; HEIGHT: 17.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=23>Sun 3/4 - Sat 3/10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #33cccc; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=20>Sun 3/4 - Sat 3/10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #339966; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=20>Sun 3/11 - Sat 3/17</TD></TR></TBODY></TABLE>

Please Help! Thank you!
 
Last edited:

Excel Facts

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

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top