Calculating Number of Days in Quarter and More

[kiyo052]

I need a formula for I1 and I2 based on the Quarter given in G1

What I figured out so far is that I need a MATCH formula and NETWORKDAYS

For I1 I was thinking a MATCH "Q"&G1 to a range of A1:D1

Since today is 1/20/17 then the example should be these results:
If G1=1 than I1= 15 and I2= 65
If G1=2 than I1=-51 and I2=65
If G1=3 than I1=-116 and I2= 65
If G4=4 than I1=-181 and I2=65

I know there are holidays and I can calculate that later.

Please let me know if this is at all possible.

Thank you!!!!

	A	B	C	D	E	F	G	H	I
1	Q1	Q2	Q3	Q4		Quarter	1	Days Lapsed
2	1/1/2017	4/1/2017	7/1/2017	10/1/2017		Year	2017	Days in Quarter
3	3/31/2017	6/30/2017	9/29/2017	12/31/2017
4

<tbody>
</tbody>

​

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Q1	Q2	Q3	Q4		Quarter	1	Days Lapsed	15
2	1/1/2017	4/1/2017	7/1/2017	10/1/2017		Year	2017	Days in Quarter	65
3	3/31/2017	6/30/2017	9/29/2017	12/31/2017
Sheet2

Cell Formulas
Range	Formula
I1	=NETWORKDAYS(INDEX(A2:D2,MATCH("Q"&G1,A1:D1,0)),TODAY())
I2	=NETWORKDAYS(INDEX(A2:D2,MATCH("Q"&G1,A1:D1,0)),INDEX(A3:D3,MATCH("Q"&G1,A1:D1,0)))

 

[Tetra201]

Is this what you need?

I1 =NETWORKDAYS(DATE($G$2,($G$1-1)*3+1,1),TODAY())
I2 =NETWORKDAYS(DATE($G$2,($G$1-1)*3+1,1),DATE($G$2,$G$1*3+1,0))

 

[Marcelo Branco]

Or

I1
=NETWORKDAYS(INDEX($A$2:$D$2,G1),TODAY())

I2
=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:G3,G1))

Hope this helps

M.

 

[63falcondude]

Or

I1
=NETWORKDAYS(INDEX($A$2:$D$2,G1),TODAY())

I2
=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:G3,G1))

Hope this helps

M.

Duh... I didn't even need the match part. Nice.

 

[Marcelo Branco]

I2
=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:G3,G1))

oops... typo!

Should be
I2
=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:D3,G1))

M.

 

[kiyo052]

This really helps! Thank you!

I thought I could manipulate your formulas to include holidays I include but I guess I'm not that skilled.

Is there a way to included holidays? The holiday section could increase depending on what the boss wants to included (i.e. black friday, xmas eve, new years eve, etc.)

	A	B	C	D	E	F	G	H	I
1	Q1	Q2	Q3	Q4		Quarter	1	Days Lapsed
2	1/1/2017	4/1/2017	7/1/2017	10/1/2017		Year	2017	Days in Quarter
3	3/31/2017	6/30/2017	9/30/2017	12/31/2017
4
5	1/2/2017	7/4/2017	7/4/2017	11/23/2017
6			9/4/2017	11/24/2017
7				12/24/2017
8				12/25/2017
9

<tbody>
</tbody>

 

[63falcondude]

I would put all of your holidays in one column off to the side somewhere. For example (using Marcelo's formulas):

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Q1	Q2	Q3	Q4		Quarter	1	Days Lapsed	14		Holidays
2	1/1/2017	4/1/2017	7/1/2017	10/1/2017		Year	2017	Days in Quarter	64		1/2/2017
3	3/31/2017	6/30/2017	9/30/2017	12/31/2017							7/4/2017
4											9/4/2017
5											11/23/2017
6											11/24/2017
7											12/24/2017
8											12/25/2017
Sheet3

Cell Formulas
Range	Formula
I1	=NETWORKDAYS(INDEX($A$2:$D$2,G1),TODAY(),K2:K8)
I2	=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:D3,G1),K2:K8)

 

[Marcelo Branco]

To consider the holidays try

I1
=NETWORKDAYS(INDEX($A$2:$D$2,G1),TODAY(),INDEX(A5:D20,0,G1))

I2
=NETWORKDAYS(INDEX(A2:D2,G1),INDEX(A3:D3,G1),INDEX(A5:D20,0,G1))

The number 20 is arbitrary - adjust

M.