Dynamic Formula for remaining value based on date

ShannonL · 05/19/23

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.

Deric · 05/19/23

@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 · 05/23/23

@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 · 05/24/23

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)

Deric · 05/19/23

@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)))

ShannonL · 05/22/23

@Deric This is awesome! Thanks for this. Can you explain what the 4, 7, & 10 numbers mean in the formula? I can dabble a bit in creating basic formulas but definitely not an expert.

Deric · 05/23/23

@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.

ShannonL · 05/24/23

Makes sense. This was very helpful. Thanks!

Paul Newcome · 05/24/23

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)

ShannonL · 05/24/23

Thanks for that tip @Paul Newcome

Paul Newcome · 05/25/23

Sure thing. The important part is that it works. @Deric's formula doesn't need to be changed or anything, but if you ever need to do something similar in the future, that should help.

ShannonL · 07/01/24

@Deric As it currently is written the formula will only calculate for that year. I also have values in other columns labeled [18q1]:[22q4]. Is there a way to expand this formula so that it sums all the quarters prior to [23q1] without having to type in each column?

For example, I have text/number columns that represent quarters of the year dating back to 2018 (i.e. 22Q4, 22Q3). I want to be able to sum the cost across the quarters in each row, before today's date and subtract that from the total [Planned Billing] to find what's remaining. Each quarterly column has a different dollar amount in the cell. The rows in the [Remaining Billing] column should change every quarter based on the current date. I'm starting to think I may need a helper column.

Attached is a screen shot of the sheet. Any help is much appreciated.

SS_example.png

Uploaded Jul 1, 202461.53 KB