Trying to record a percent change based on dates.

  • Hello all, this is my first post on here and I hope someone can help me out.


    I have a workbook comprised of 6 sheets.

    Sheet 1 is the dashboard

    Sheet 2 is the hidden calculations

    Sheet 3 is checklist 1

    Sheet 4 is checklist 2

    Sheet 5 is checklist 3

    Sheet 6 is checklist 4

    On sheet 2 (the hidden calculations page) I have a simple table that calculates the total overall items and the mandatory items (not all items are mandatory) per checklist.

    On the same table I calculate the completed overall items and the completed mandatory items.

    Then again on the same table I calculate the percentage of each respectively.


    A user can open any of the 4 checklists at any time and complete a task. We then have a bi-weekly meeting every other Friday to discuss activities or help needed where the user presents the checklist completion percentage.


    What I need help with is the percentage change from meeting to meeting without the user having to do any manual calculations.

    I had the idea that I could somehow use the function Today() in a cell to always give me the current date. Then use a column to list every other Friday for the next "X" years.

    Then, with some VBA code I could record the value located in cell G8 (where I have the cumulative mandatory percent) on a given day (1/3/2020, the first Friday of this year) into cell D12 (where I want it stored).

    Then have this repeat on 1/17/2020 (the 3rd Friday of this year) taking the new value in G8 and storing it in D13, and so on.


    Simultaneously I'd like to repeat this action for the value in H8 (the cumulative overall percent) and have it stored in E12, E13, etc.