Help with referencing column data of a table depending on todays date.

Copper Contributor

Greetings all, 

 

I am attempting to determine the present value or planned value of a project at a given point in time. I have a table to data that contains data (in example Column O-S) for the projects present value at the end of each month. I acknowledge it is a bit confusing since the date is the first of every month but that column would represent all of the cost the project should incur over the course of that month. 

 

My plan to derive the PV or Present value at any point in time was to determine the PV given the current day is listed below:

 

 Based on Column Letters:

 

Jawood_4-1718764212270.png

 

 

Based on Description

 

Jawood_5-1718764212270.png

 

 

Delta PV or change in PV is:

 

Jawood_6-1718764212270.png

 

 

Prog. In Mo is progress or % of Workdays completed this month. I planned to determine this as listed below:

 

Jawood_7-1718764212270.png

 

 ;

 

Where Wd So Far is =Networkdays(1st Day of month, Today)

 

WD Mo Total is = NetworkDays(1st Day of month , Last Day or month)

 

 

If I haven't lost you so far, My next need was to populate the Last Month PV & Current Mo PV columns. My aim was to use xlookup or and index match but I seem to be missing something to get this figured out. The aim is to gradually add the PV daily as the project progresses. Any insight you can give would be greatly appreciated. 

 

ProjectCustomerPVToday1st DoMLast DoMLast MonthWD So FarWD Mo TotalProg. In Mo
12534First06/18/246/1/246/30/245/1/2412200.6
19384Second06/18/246/1/246/30/245/1/2412200.6
13094Start06/18/246/1/246/30/245/1/2412200.6
18503Turner06/18/246/1/246/30/245/1/2412200.6

 

Last Month PVCurrent Month PVDelta PVSpacer5/1/246/1/247/1/248/1/249/1/24
  0 10050090015003000
  0 30060070012003500
  0 505070015002000
  0 12540075013004500

 

This is a sorry attempt to get an example amount of data in here since the form will not let me upload the excel doc. 

0 Replies