Question

Customer	Order no.	Anchor	Invoice Date	Amount	Days Outstanding	Status
Baker	10643	17	10/30/2018	$   586.00
Baker	10644	57	9/20/2018	$   972.00
Birkey	10645	156	6/13/2018	$   503.00
Bordewick	10646	66	9/11/2018	$   619.00
Bordewick	10647	30	10/17/2018	$   650.00

In this Excel file we have a list of customers, some with multiple orders and individual invoices for each order. The goal of this project is to make a dynamic aging schedule using our knowledge of functions and Pivot Tables.

First, we will need to calculate the number of days the invoice has been outstanding. To do this, create an IF function in Column F to calculate the number of days outstanding based on today’s date and the Invoice Date. If the date is past today’s date, put a zero.
HINT: Days outstanding = number of days between the invoice date and today’s date
NOTE: today’s date should be volatile

*************I Tried using =today()-d2 in column f but the results were not accurate to the assignment

Answer 1

A	B	C	D	E	F	G	H	I	J	K
Customer	Order No.	Anchor	Invoice Date	Amount ($)	Days Outstanding	Not Due	0-30 days	31-60 days	61-90 days	>90 days
Baker	10643	17	30-10-2018	586	17	0	586	0	0	0
Baker	10644	57	20-09-2018	972	57	0	0	972	0	0
Birkey	10645	156	13-06-2018	503	156	0	0	0	0	503
Bordewick	10646	66	11-09-2018	619	66	0	0	0	619	0
Bordewick	10647	30	17-10-2018	650	30	0	650	0	0	0
				3330			1236	972	619	503
F=TODAY()-D3
G=IF(F3=0,E3,0)
H=IF(D3<TODAY(),(IF(TODAY()-D3<=30,E3,0)),0)
I=IF(AND(TODAY()-D3<=60,TODAY()-D3>30),E3,0)
J=IF(AND(TODAY()-D3<=90,TODAY()-D3>60),E3,0)
K=IF(F3>90,E3,0)