code5onyou

New Member
Joined
May 31, 2011
Messages
4
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

code5onyou

New Member
Joined
May 31, 2011
Messages
4
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...
 
Upvote 0

code5onyou

New Member
Joined
May 31, 2011
Messages
4
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>
 
Upvote 0

code5onyou

New Member
Joined
May 31, 2011
Messages
4
Sorry I bordered it, but it didn't come through with the borders for some reason... I am by no means a cpu guru!
 
Upvote 0

Forum statistics

Threads
1,187,106
Messages
5,961,626
Members
438,556
Latest member
darrylburge

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
Top