Cumulative Budget Formula Based on Today's Date
![ClaireWallace](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hello,
I am trying to create a formula that will do one of two things, depending on today's date in relationship to the end of the quarter
1) If it is less than the end of the quarter, I want the budget to stay at its baseline.
2) If today's date is past the end of the quarter, I want the surplus or deficit budget amount from Q1 to be summed with the Q2 budget baseline.
Here is the formula I am currently using. I'm hitting an unparseable wall in the second portion of my formula. Thank you!
=SUMIF(TODAY(), >[Q1 Closed]@row, [T&E Q1 S/D Allocated to Remaining Q]@row + [T&E Q2 Budget Baseline]@row), SUMIF(TODAY(), <[Q1 Closed]@row, [T&E Q2 Budget Baseline]@row)
Answers
-
It looks like you are trying to use two
SUMIF
functions together in a single formula, but they are not properly combined.You can use the
IF
function to create a conditional statement that checks whether today's date is greater than the end of the quarter. If it is, the formula will sum the Q1 surplus/deficit with the Q2 budget baseline. If it is not, the formula will simply return the Q2 budget baseline.Here's an example formula that you can adapt to your specific case:
=IF(TODAY() > [Q1 Closed]@row, [T&E Q1 S/D Allocated to Remaining Q]@row + [T&E Q2 Budget Baseline]@row, [T&E Q2 Budget Baseline]@row)
This formula uses the
IF
function to check whether today's date is greater than the end of the quarter. If it is, the formula adds the Q1 surplus/deficit to the Q2 budget baseline using the+
operator. If today's date is less than or equal to the end of the quarter, the formula simply returns the Q2 budget baseline.Note that you will need to replace the cell references in this example formula with the appropriate cell references for your specific case.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!