Forum Discussion

Jawood's avatar
Jawood
Copper Contributor
Jun 19, 2024

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

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:

 

 

 

Based on Description

 

 

 

Delta PV or change in PV is:

 

 

 

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

 

 

 ;

 

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. 

1 Reply

  • 1. Setup for PV Calculation
    Let's assume the table data starts in cell A1:

    Column G: Last Month PV
    Column H: Current Month PV
    Column I: Delta PV
    2. Determine PV for Last Month
    To get the PV for the last month, use INDEX and MATCH to find the value corresponding to the last month's date:

    excel
    Copy code
    =INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
    This formula assumes:

    Row 1 has dates for PV (e.g., 5/1/24, 6/1/24, etc.).
    DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1) gives the first day of last month.
    $L$1:$P$1 is the range of dates in the header row.
    3. Determine PV for Current Month
    To get the PV for the current month:

    excel
    Copy code
    =INDEX($L$1:$P$1, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
    This formula uses the first day of the current month to find the corresponding PV value.

    4. Calculate Delta PV
    To calculate the Delta PV as the difference between the current month and last month PV:

    excel
    Copy code
    =H2 - G2
    Where H2 is the Current Month PV and G2 is the Last Month PV.

    5. Calculate Progress in Month
    WD So Far: Number of workdays so far in the current month:

    excel
    Copy code
    =NETWORKDAYS(E2, TODAY())
    Where E2 is the first day of the month.

    WD Mo Total: Total workdays in the current month:

    excel
    Copy code
    =NETWORKDAYS(E2, F2)
    Where E2 is the first day of the month and F2 is the last day of the month.

    Prog. In Mo: Progress percentage:

    excel
    Copy code
    =WD So Far / WD Mo Total
    6. Example Formulas in Excel
    Assuming the example data starts from row 2:

    Last Month PV (G2):
    excel
    Copy code
    =INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), $L$1:$P$1, 0))
    Current Month PV (H2):
    excel
    Copy code
    =INDEX($L$2:$P$2, MATCH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1), $L$1:$P$1, 0))
    Delta PV (I2):
    excel
    Copy code
    =H2 - G2
    WD So Far (H2):
    excel
    Copy code
    =NETWORKDAYS(D2, TODAY())
    WD Mo Total (I2):
    excel
    Copy code
    =NETWORKDAYS(D2, E2)
    Prog. In Mo (J2):
    excel
    Copy code
    =H2 / I2

Resources