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 "resums" 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

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

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.

This can also be simplified a bit to cut down on typing and backend 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

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

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.

Makes sense. This was very helpful. Thanks!

This can also be simplified a bit to cut down on typing and backend 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)

Thanks for that tip @Paul Newcome

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.

@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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!