Date Calculation

[snjpverma]

Hi Experts,

My data is as below. is the output in Column G possible without helper column C?

	A	B	C	D	E	F	G
Name	Dates	Difference				Output average
John			John
Tom			Tom
Von			Von
John
Tom
Von
John
Tom
Von
John
Tom
Von

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:73px;"><col style="width:73px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:104px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]4/29/2018[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]22.5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]4/16/2018[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]22.25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]3/13/2018[/TD]
[TD="align: right"]38[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]4/4/2018[/TD]
[TD="align: right"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]3/18/2018[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]3/18/2018[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]3/29/2018[/TD]
[TD="align: right"]26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]4/5/2018[/TD]
[TD="align: right"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]4/15/2018[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]3/28/2018[/TD]
[TD="align: right"]27[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]3/13/2018[/TD]
[TD="align: right"]38[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =NETWORKDAYS(B2,TODAY()-1) G2 =AVERAGEIF($A$2:$A$13,F2,$C$2:$C$13) G3 =AVERAGEIF($A$2:$A$13,F3,$C$2:$C$13) G4 =AVERAGEIF($A$2:$A$13,F4,$C$2:$C$13) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[snjpverma]

Just to highlight that the formula used column C has been mentioned at the bottom in Post #1 . It is a networkdays function which I use to find the difference from yesterday to the Date in Column B.

 

[snjpverma]

any formula for this one? The only thing I want is: get the average of networkdays without the helper column C.
Column C is the networkdays from Date in column B to Yesterday.

This is basically what I want.

=Averageif(A$2:A13,F2,Networkdays(B2:B13),Yesterday)

But the networkdays doesn't accept the array function in this manner.

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF($A$2:$A$13=F2,NETWORKDAYS($B$2:$B$13+0,TODAY()-1)))

 

[snjpverma]

wow that's brilliant Aladin. I was trying this for so long.
This forum is full of genius people. Whenever I come here, I learn something new.