Date Calculation

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Hi Experts,

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

ABCDEFG
1NameDatesDifference Output average
2John4/29/20184 John22.5
3Tom4/16/201814 Tom22.25
4Von4/13/201815 Von27
5John3/13/201838
6Tom4/4/201822
7Von3/18/201834
8John3/18/201834
9Tom3/29/201826
10Von4/5/201821
11John4/15/201814
12Tom3/28/201827
13Von3/13/201838

<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>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
In G2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=AVERAGE(IF($A$2:$A$13=F2,NETWORKDAYS($B$2:$B$13+0,TODAY()-1)))
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,125
Messages
5,640,261
Members
417,132
Latest member
Srw123

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
Top