Date Range

[code5onyou]

I currently have a worksheet that has names, dates, misc data, etc. I need to see if Excel can do the following:

I have the calendar broken up into 13 sections (28 days each, of course they could use a 12 month calendar to make it easy for me!) and each section of the calendar has a name, ie Jan 2-Jan 29 is called DP1, Jan 30 - Feb 26 is DP2, Feb 27 - Mar 26 is DP3, etc...

My date is in Column B and I need to make Column E auto populate with the respective DP based upon the date that is input into Column B. Is there a code that I can write or a command from another sheet that will allow me to define certain dates to a certain value?

I am at a loss... I dunno if this is even possible, but it seems like a fairly logical expression, I just can't figure it out!

Thanks

 

[VoG]

An example of what you have and what you want would help.

Look here http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[code5onyou]

That looks like it is a plug in for Excel, I am on a work computer and I do not have SysAd privileges to install any tool bars...

Oh and I forgot to mention, I am using Excel 2003..

Thanks though...

 

[VoG]

Put borders around your data, copy then paste here.

 

[code5onyou]

Sample of my data in the current sheet:

<table x:str="" style="border-collapse: collapse; width: 250pt;" border="0" cellpadding="0" cellspacing="0" width="333"><tbody><tr style="height: 15pt;" height="20"><tr style="height: 15pt;" height="20"><td class="xl65" style="height: 15pt; width: 55pt;" height="20" width="73">Employee:</td><td class="xl65" style="border-left: medium none; width: 51pt;" width="68">Date:</td><td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Type:</td><td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Hours:</td><td class="xl65" style="border-left: medium none; width: 48pt;" width="64">DP:</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Smith</td><td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="40555" align="right">1/12/2011</td><td class="xl68" style="border-top: medium none; border-left: medium none;">CE</td><td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">8</td><td class="xl68" style="border-top: medium none; border-left: medium none;">DP1</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Jones</td><td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="40561" align="right">1/18/2011</td><td class="xl68" style="border-top: medium none; border-left: medium none;">Annual</td><td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">6</td><td class="xl68" style="border-top: medium none; border-left: medium none;">DP1</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Johnson</td><td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="40576" align="right">2/2/2011</td><td class="xl68" style="border-top: medium none; border-left: medium none;">CE</td><td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">8</td><td class="xl68" style="border-top: medium none; border-left: medium none;">DP2</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Davis</td><td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="40595" align="right">2/21/2011</td><td class="xl68" style="border-top: medium none; border-left: medium none;">Renew</td><td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">4</td><td class="xl68" style="border-top: medium none; border-left: medium none;">DP2</td></tr><tr style="height: 15pt;" height="20"><td class="xl66" style="height: 15pt; border-top: medium none;" height="20">Thomas</td><td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="40604" align="right">3/2/2011</td><td class="xl68" style="border-top: medium none; border-left: medium none;">Annual</td><td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">16</td><td class="xl68" style="border-top: medium none; border-left: medium none;">DP3</td></tr></tr></tbody></table>
Sample of the data I need to auto populate in that if a certain date is equal to or falls between the two dates then it is equal to that DP#

<table x:str="" style="border-collapse: collapse; width: 154pt;" border="0" cellpadding="0" cellspacing="0" width="205"><col style="width: 55pt;" width="73"> <col style="width: 51pt;" width="68"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 55pt;" x:num="40545" align="right" height="20" width="73">1/2/2011</td> <td class="xl65" style="border-left: medium none; width: 51pt;" x:num="40572" align="right" width="68">1/29/2011</td> <td class="xl66" style="border-left: medium none; width: 48pt;" width="64">DP1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" x:num="40573" align="right" height="20">1/30/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" x:num="40600" align="right">2/26/2011</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">DP2</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; border-top: medium none;" x:num="40601" align="right" height="20">2/27/2011</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" x:num="40628" align="right">3/26/2011</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">DP3</td> </tr> </tbody></table>

 

[code5onyou]

Sorry I bordered it, but it didn't come through with the borders for some reason... I am by no means a cpu guru!