Dynamic Formula for remaining value based on date

I'm trying to create a formula to show remaining billing from a range of costs across different columns for each project number in a pivot sheet. Ideally I would like the "Remaining Billing" column to be dynamic so after a certain date the column "re-sums" the remaining cost.

Example:

After Dec 31 (Total Planned Billing = Remaining Billing)

After March 31 (Total Planned Billing - 23Q1 = Remaining Billing)

After June 30 (Total Planned Billing - (23Q1 + 23Q2) = Remaining Billing)

etc...

Any guidance would be appreciated.


Best Answers

  • Deric
    Deric ✭✭✭✭✭
    edited 05/19/23 Answer ✓

    @ShannonL This should work:

    =IF(MONTH(TODAY()) < 4, [Total Planned Billing]@row, IF(MONTH(TODAY()) < 7, [Total Planned Billing]@row - [23q1]@row, IF(MONTH(TODAY()) < 10, [Total Planned Billing]@row - [23q1]@row - [23q2]@row, [Total Planned Billing]@row - [23q1]@row - [23q2]@row - [23q3]@row)))

  • Deric
    Deric ✭✭✭✭✭
    edited 05/23/23 Answer ✓

    @ShannonL

    The MONTHS formula uses the numerical position of the month in calculations: January = 1, February = 2, March = 3, April = 4, etc.

    I used the numbers to mark the quarters so less than 4 equal q1, less than 7 equal q2, less than 10 equal q3, and everything else is q4. Note that you don't have to specify "greater than 3 and less than 7" for q2 because the formula doesn't check for q2 if it is already q1, i.e., the formula is ordered from most restrictive to least restrictive terms so it only checks for the next quarter if it doesn't meet the criteria for the previous quarter.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    This can also be simplified a bit to cut down on typing and back-end processing.


    =[Total Planned Billing]@row - IF(MONTH(TODAY())>= 4, [23q1]@row, 0) - IF(MONTH(TODAY())>= 7, [23q2]@row, 0) - IF(MONTH(TODAY())>= 10, [23q3]@row, 0)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!