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