A possible formula or lookup table

T

tpsychi

New Member
Joined
Jan 23, 2009
Messages
9
Excel Workbook
ABCDE
1exam dateexam qtr10th qtr from exam date
24/1/2008Spring 2008Fall 201110/1/2008Fall 2008
31/1/2009Winter 2009
4Need a formula that will add 10 quarters to 4/1/084/1/2009Spring 2009
5or Spring 2008 and give us the result Fall 2011.10/1/2009Fall 2009
61/1/2010Winter 2010
74/1/2010Spring 2010
810/1/2010Fall 2010
91/1/2011Winter 2011
104/1/2011Spring 2011
1110/1/2011Fall 2011
121/1/2012Winter 2012
134/1/2012Spring 2012
1410/1/2012Fall 2012
Sheet1


Need help with how to find the 10th quarter for each exam date.


thanks

tpsychi
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Times New Roman,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 113px"><COL style="WIDTH: 113px"><COL style="WIDTH: 138px"><COL style="WIDTH: 113px"><COL style="WIDTH: 113px"><COL style="WIDTH: 71px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Exam Date</TD><TD style="TEXT-ALIGN: center">Exam Qtr</TD><TD style="TEXT-ALIGN: center">10th Qtr from Exam Date</TD><TD style="TEXT-ALIGN: center">Exam Qtr</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">04/01/2008</TD><TD style="BACKGROUND-COLOR: #ffff00">Spring 2008</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">10/01/2011</TD><TD>Fall 2011</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">04/01/2008</TD><TD style="BACKGROUND-COLOR: #ffff00">Spring 2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">10/01/2008</TD><TD>Fall 2008</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2009</TD><TD>Winter 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2009</TD><TD>Spring 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2009</TD><TD>Fall 2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2010</TD><TD>Winter 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2010</TD><TD>Spring 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2010</TD><TD>Fall 2010</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2011</TD><TD>Winter 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2011</TD><TD>Spring 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2011</TD><TD>Fall 2011</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2012</TD><TD>Winter 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">04/01/2012</TD><TD>Spring 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">10/01/2012</TD><TD>Fall 2012</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">01/01/2013</TD><TD>Winter 2013</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=VLOOKUP(A2,$E$2:$F$16,2,FALSE)</TD></TR><TR><TD>C2</TD><TD>=INDEX(E2:E16,MATCH(A2,$E$2:$E$16,0)+10)</TD></TR><TR><TD>E2</TD><TD>=A2</TD></TR><TR><TD>F2</TD><TD>=LOOKUP(MONTH(E2),{1,4,7,10},{"Winter ","Spring ","Summer ","Fall "})&YEAR(E2)</TD></TR><TR><TD>E3</TD><TD>=DATE(YEAR(A2),MONTH(A2)+LOOKUP(MONTH(A2),{1,4,7},{3,6,3}),DAY(A2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

I hope it works.
 
Last edited:
Upvote 0
if i add 10 quarters to 1 apr 2008 i do not get your result however i try, even counting on my fingers

here is a formula to return a date 10 quarters (10*3 months) from a cell date, you can build this into your existing formula
=(DATE(YEAR(A32)+(10*3)/12,MONTH(A32)+MOD(10*3,12),DAY(A32)))
 
Upvote 0
thanks for everyone's help i forgot to mention about skipping the summer quarter so sorry for some confusion.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Payroll Forecasting Sheet-How to setup table by acct number and returning values closest to today's date.
Replies
1
Views
319
awiley1
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
425
Peter_SSs
Peter_SSs
S
  • Question Question
Sumproduct - to count occasions in a date range
Replies
3
Views
409
Cubist
Cubist
V
  • Question Question
Need help with excel formulas
Replies
5
Views
223
victoriar
V
M
  • Solved
Lookup formula
Replies
3
Views
451
Fluff
Fluff

Forum statistics

Threads
1,222,405
Messages
6,165,867
Members
451,989
Latest member
DannyBoy1977

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