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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!