Cumulative Budget Formula Based on Today's Date

ClaireWallace

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)

Frank B.

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.