Pivot to Show Monthly Average Days Between two Dates

[jarett]

Trying to display a data set more efficiently/professional. To start off I have two date fields that I am getting the amount of days between those two dates, and I was going to have a running average field. However, I think a pivot table would be good for this, except my pivot table skills/comprehension are elementary at best. I would like to have the pivot be able to breakdown the average days between the two fields by month, then I think I could figure out how to expand on other metrics I want. The two fields I want to focus on right now are column C & D. Anyone has any good examples or tips on how to get this started.

AverageDaystoInvoice.xlsx
	A	B	C	D	E	F	G	H	I
1	InvoiceNo	SalesOrderNo	InvoiceDate	OrderDate	ShipDate	SalespersonNo	BillToName	DateCreated	Column1
2	0000604	8242152	3/14/2023	3/7/2023	3/9/2023	MEM	EXCEL MODULAR SCAFFOLD	3/14/2023	6
3	0000605	8242917	3/14/2023	3/8/2023	3/10/2023	MEM	EXCEL MODULAR SCAFFOLD	3/14/2023	5
4	0000622	8242146	3/14/2023	3/7/2023	3/9/2023	MEM	EXCEL MODULAR SCAFFOLD	3/14/2023	6
5	0000623	8243399	3/14/2023	3/8/2023	3/13/2023	MEM	EXCEL MODULAR SCAFFOLD	3/14/2023	5
6	0000666	0000476	3/14/2023	3/13/2023	3/27/2023	SVA	WESTLAKE PIPE & FITTINGS	3/14/2023	2
7	0000670	0000095	3/14/2023	3/7/2023	3/14/2023	NRL	THE RIVERFRONT RESTAURANT	3/14/2023	6
8	0000677	0000088	3/14/2023	3/7/2023	3/14/2023	BJL	CANNON COMPRESSION SERVICES	3/14/2023	6
9	0000678	0000160	3/14/2023	3/7/2023	3/14/2023	MPL	HYDRO CARBON FLOW SPECIALISTS	3/14/2023	6
10	0000679	0000243	3/14/2023	3/8/2023	3/13/2023	JB	QUALITY ENERGY SERVICE LLC	3/14/2023	5
11	0000684	0000191	3/14/2023	3/8/2023	3/14/2023	ACT	HILLER OFFSHORE SERVICES	3/14/2023	5
12	0000687	0000292	3/14/2023	3/9/2023	3/13/2023	JBL	JBI HELICOPTER SERVICES	3/14/2023	4
AR_InvoiceHistoryHeader

Cell Formulas
Range	Formula
I2:I12	I2	=NETWORKDAYS([@OrderDate],[@InvoiceDate],1)

 

[awoohaw]

what month do you want to use for the grouping? The Order Date month or the Invoice Date Month?

 

[jarett]

what month do you want to use for the grouping? The Order Date month or the Invoice Date Month?

The order date month.

 

[jarett]

A little more googling lead me to some examples and I think I got the display in the pivot correct, or so it looks right. Curious if this is how you would have done it, @awoohaw.

AverageDaystoInvoice.xlsx
	A	B	C
1	InvoiceDate	(Multiple Items)
2
3	Row Labels	Count of OrderDate	Average of DaystoInvoice
4	Mar	3794	20.29
5	Apr	3758	15.92
6	May	4536	15.14
7	Jun	3864	13.11
8	Jul	4358	13.20
9	Aug	3863	9.05
10	Sep	555	4.71
11	Grand Total	24728	14.20
AvDaysInv

 

[awoohaw]

You did not give other months data in your initial post. I would do something similar to what you have.
I'm pleased you researched more.

Best Wishes.

 

[jarett]

Here is the article that I used to based my solution on,
Create a pivot table depending of two dates