Calculating number of days left in a quarter

[Captain825]

This formula calculates the total number of days in the month based on a user inputted date.
=DAY(DATE(YEAR(B13),MONTH(B13)+1,1)-1)


This formula calculates the number of days remaining in a month based on a user inputted date.
=DATE(YEAR(B13),MONTH(B13)+1,0)-B13


My problems....
1. I have to determine the same information but based on quarters.

2. The quarters for my sheet either run (Jan 1 - Mar 31) or (Feb 1 - Apr 30). Is it possible to have one formula handle both types of quarters or do I need two separate formulas?

3. How would the user select which quarter (ending in Mar 31 or Apr 30) the entry is regarding? I would imagine the user would input the ending date of the quarter and then the formula would work towards the beginning of that quarter? Don't know.

Any help would be very appreciated.

Thanks,
Adrian

 

[c_m_s_jr]

Assuming you build you quarter beginning and ending dates off to the side then:

Excel Workbook
	B	C	D	E	F
1				1/1/2009	3/31/2009
2				4/1/2009	6/30/2009
3				7/1/2009	9/30/2009
4				10/1/2009	12/31/2009
5
6
7
8
9
10
11
12	User Date			Days In Qtr
13	11/23/2009			39
Sheet1

 

[Captain825]

Thank you very much!

Adrian

 

[c_m_s_jr]

I am sure there is a way to do it without the helpers, but I just couldn't figure it out. If you set them up the way I have they at least change to the corresponding year based on the user input...

 

[Captain825]

Your suggestion worked great! The only thing I am missing is figuring out how many days there are in a particular quarter. For example, if the user inputs 4/4/09, I need a formula that determines the total number of days in that quarter (from 4/1/09 - 6/30/09).

This the formula I use to determine the number of days in a month based on user input.
=DAY(DATE(YEAR(B13),MONTH(B13)+1,1)-1)

Is there an equivalent for quarters?

Thanks
Adrian

 

[c_m_s_jr]

Just add a column that calculates the days:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 83px"><COL style="WIDTH: 83px"><COL style="WIDTH: 64px"><COL style="WIDTH: 83px"><COL style="WIDTH: 83px"><COL style="WIDTH: 68px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">1/1/2009</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">3/31/2009</TD><TD style="TEXT-ALIGN: right"> 90.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">4/1/2009</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">6/30/2009</TD><TD style="TEXT-ALIGN: right"> 91.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">7/1/2009</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">9/30/2009</TD><TD style="TEXT-ALIGN: right"> 92.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">10/1/2009</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">12/31/2009</TD><TD style="TEXT-ALIGN: right"> 92.00 </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">User Date</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Days Remain In Qtr</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Days in Qtr</TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">9/18/2009</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana"> </TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">39</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">9/30/2009</TD><TD> </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>E1</TD><TD>=DATE(YEAR(C13),1,1)</TD></TR><TR><TD>F1</TD><TD>=EOMONTH(E1,2)</TD></TR><TR><TD>G1</TD><TD>=(F1+1)-E1</TD></TR><TR><TD>E2</TD><TD>=DATE(YEAR(C13),4,1)</TD></TR><TR><TD>F2</TD><TD>=EOMONTH(E2,2)</TD></TR><TR><TD>G2</TD><TD>=(F2+1)-E2</TD></TR><TR><TD>E3</TD><TD>=DATE(YEAR(C13),7,1)</TD></TR><TR><TD>F3</TD><TD>=EOMONTH(E3,2)</TD></TR><TR><TD>G3</TD><TD>=(F3+1)-E3</TD></TR><TR><TD>E4</TD><TD>=DATE(YEAR(C13),10,1)</TD></TR><TR><TD>F4</TD><TD>=EOMONTH(E4,2)</TD></TR><TR><TD>G4</TD><TD>=(F4+1)-E4</TD></TR><TR><TD>F13</TD><TD>=VLOOKUP(C13,$E$1:$G$4,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[barry houdini]

If quarter 1 begins on 1st Jan then this formula will give the number of days in the quarter

=SUM(DATE(YEAR(B13),CEILING(MONTH(B13),3)+{-2,1},0)*{-1,1})

 

[c_m_s_jr]

And there you have it, no helper columns! Nice!!!

 

[Captain825]

Excellent!!!!

Thank you c_m_s_jr and barry houdini both very much!

Adrian