Account Date Periods 1 to 12

Trevor G

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,735
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
I am looking to resolve an issue with date periods (not the normal is it a Monday etc). I have a spreadsheet that contains Dates in Column I, in column A I want to add some text if the date falls within a period to say "AP 1" or "AP 2" etc. My date periods are not normal Month Begin and End.
Dates for Period 1 are 03/05/2008 to 12/06/2006 Period 2 are 13/06/2008 to 12/07/2008. I understand it will need a loop of some kind to look through Column I and return the text to Column A. Can anyone assist me with the VBA code to fulfill this!

Thanks Trevor G
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
You shouldn't need VBA for this, can you list your AP ranges ?
The first period you mention exceeds one month (unless it should read 13/05 as opposed to 03/05)

Assuming they all run 13th to 12th with May being AP1, the below would return APx

="AP"&LOOKUP(MONTH(I2)+IF(DAY(I2)<13,-1,0),{1,2,3,4,5,6,7,8,9,10,11,12},{9,10,11,12,1,2,3,4,5,6,7,8})

However, you'd need to advise you how would want to handle AP12 in prior year etc...
 
Last edited:
Upvote 0
The two periods you indicated are in different years.Is this correct ?
 
Upvote 0
Pending responses to mine and arthurbr's posts, I noted also my formula was missing one required criteria (that of 01-Jan to 12-Jan) which is rectified below.

="AP"&LOOKUP(MONTH(I8)+IF(DAY(I8)<13,-1,0),{0,1,2,3,4,5,6,7,8,9,10,11,12},{8,9,10,11,12,1,2,3,4,5,6,7,8})
 
Upvote 0
Trevor G said:
I am looking to resolve an issue with date periods (not the normal is it a Monday etc). I have a spreadsheet that contains Dates in Column I, in column A I want to add some text if the date falls within a period to say "AP 1" or "AP 2" etc. My date periods are not normal Month Begin and End.
Dates for Period 1 are 03/05/2008 to 12/06/2006 Period 2 are 13/06/2008 to 12/07/2008. I understand it will need a loop of some kind to look through Column I and return the text to Column A. Can anyone assist me with the VBA code to fulfill this!

Thanks Trevor G
Click to expand...

Oops I did get the year wrong when I wrote this. I thank you for your answers and believe you have helped me resolve this issue.

The actual period dates are indicated below:

<TABLE style="WIDTH: 199pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=265 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>AP</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 67pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=89>Start</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 84pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=112>End</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39536">29-Mar-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39570">02-May-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39571">03-May-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39598">30-May-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39599">31-May-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39626">27-Jun-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>4</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39627">28-Jun-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39661">01-Aug-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>5</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39662">02-Aug-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39689">29-Aug-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>6</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39690">30-Aug-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="1">01-Jan-00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>7</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39718">27-Sep-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39752">31-Oct-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>8</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39753">01-Nov-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39780">28-Nov-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>9</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39781">29-Nov-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39808">26-Dec-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>10</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39809">27-Dec-08</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39843">30-Jan-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>11</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39844">31-Jan-09</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39871">27-Feb-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>12</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39872">28-Feb-09</TD><TD class=xl67 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num="39899">27-Mar-09</TD></TR></TBODY></TABLE>
If you could look again that would be helpful
 
Last edited:
Upvote 0
The actual period dates are indicated below:

<TABLE style="WIDTH: 199pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=265 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 48pt; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=64 height=17>AP</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 67pt; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=89>Start</TD><TD class=xl65 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; WIDTH: 84pt; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" width=112>End</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>1</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39536">29-Mar-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39570">02-May-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>2</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39571">03-May-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39598">30-May-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>3</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39599">31-May-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39626">27-Jun-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>4</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39627">28-Jun-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39661">01-Aug-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>5</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39662">02-Aug-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39689">29-Aug-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>6</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39690">30-Aug-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="1">01-Jan-00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>7</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39718">27-Sep-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39752">31-Oct-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>8</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39753">01-Nov-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39780">28-Nov-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>9</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39781">29-Nov-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39808">26-Dec-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>10</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39809">27-Dec-08</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39843">30-Jan-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>11</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39844">31-Jan-09</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39871">27-Feb-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" height=17 x:num>12</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39872">28-Feb-09</TD><TD class=xl67 style="BORDER-LEFT-COLOR: #d4d0c8; BORDER-BOTTOM-COLOR: #d4d0c8; BORDER-TOP-COLOR: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #d4d0c8" x:num="39899">27-Mar-09</TD></TR></TBODY></TABLE>
If you could look again that would be helpful<!-- / message --><!-- edit note -->
 
Upvote 0
If you can I'd suggest you create a sheet called "APS", put the table you gave me into that sheet (A1 down), however, I'd suggest you add 2 additional APS

first entry in table
AP0 - 01/01/1900 -> 28/03/08
last entry in table
AP13 - 28/03/2009 -> 31/12/2999

Any date pre 29/03/08 would return AP0, any date post 27/03/09 would return AP13

Then in $A2 on your other sheet you could use:

="AP"&LOOKUP($I2,APS!$B$1:$B$15,APS!$A$1:$A$15)

Where $I2 contains your date, this formula could be copied down in A for as many rows as required.

HTH
 
Upvote 0
lasw10 said:
If you can I'd suggest you create a sheet called "APS", put the table you gave me into that sheet (A1 down), however, I'd suggest you add 2 additional APS

first entry in table
AP0 - 01/01/1900 -> 28/03/08
last entry in table
AP13 - 28/03/2009 -> 31/12/2999

Any date pre 29/03/08 would return AP0, any date post 27/03/09 would return AP13

Then in $A2 on your other sheet you could use:

="AP"&LOOKUP($I2,APS!$B$1:$B$15,APS!$A$1:$A$15)

Where $I2 contains your date, this formula could be copied down in A for as many rows as required.



HTH
Click to expand...
Thank you this works well and you have helped alot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Split Dates Between Contract and Reporting Periods
Replies
0
Views
313
*shudder*
S
Sean15
  • Question
How to make pivot table show totals for specific date range
Replies
2
Views
366
Sean15
Sean15
D
  • Question
Largest period between two dates
Replies
4
Views
231
damian_r_Home
D
S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
542
shawnbull
S
S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
454
Sallyprit
S

Forum statistics

Threads
1,214,920
Messages
6,122,272
Members
449,075
Latest member
staticfluids

Share this page

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