Formula help
Quarter 2 (April June) I need the formula that does the following:
Take the week of 5Apr + 12Apr +Apr19 and that would populate for the [actual number], the remaining weeks would be [planned]. Than dynamically the formula would auto populate each week consecutively so that it would add the current week to the sum of the actual number and so on until we reached the remaining weeks at the end of the quarter in this case 28Jun. At the end of the quarter there would be NO more [planned] since all weeks had been consumed and it would result in [actual number].
Any help with this would be apricated.
Best Answer

Hi Kelly,
I hate to be the bearer of bad news, but what you are asking is incredibly complex. Or, well, not so much complex as it is tedious (as far as the formula required, that is).
Essentially, for the [actual number] column, you are going to have to write a massive nested if statement that checks to see if today's date is greater than the date of your last datename column—in your case, June 28 because your last datename column is "28Jun". If today's date is greater than that datename column, then go ahead and add all the date columns across. The if conditions have to work backward from June 28 since the first condition to be true will fulfill the if statement.
I tried my best to give you an idea of what I am talking about. See the attached picture. In the simplest way I can put it, the formula would work like this:
 If today's date is greater than June 28, 2021, then add all the columns across from 5Apr to 28Jun.
 (To be written in the false argument of the first if.) If today's date is greater than June 21, 2021, then add all the columns across from 5Apr to 21Jun.
 (To be written in the false argument of the second if.) If today's date is greater than June 14, 2021, then add all the columns across from 5Apr to 14Jun.
 (To be written in the false argument of the third if.) So on and so forth...
The [planned] column would pretty much follow similar logic but add the columns differently.
I know that is incredibly convoluted, but that's the easiest way I can explain it. I hope that helps.
Answers

Hi Kelly,
I hate to be the bearer of bad news, but what you are asking is incredibly complex. Or, well, not so much complex as it is tedious (as far as the formula required, that is).
Essentially, for the [actual number] column, you are going to have to write a massive nested if statement that checks to see if today's date is greater than the date of your last datename column—in your case, June 28 because your last datename column is "28Jun". If today's date is greater than that datename column, then go ahead and add all the date columns across. The if conditions have to work backward from June 28 since the first condition to be true will fulfill the if statement.
I tried my best to give you an idea of what I am talking about. See the attached picture. In the simplest way I can put it, the formula would work like this:
 If today's date is greater than June 28, 2021, then add all the columns across from 5Apr to 28Jun.
 (To be written in the false argument of the first if.) If today's date is greater than June 21, 2021, then add all the columns across from 5Apr to 21Jun.
 (To be written in the false argument of the second if.) If today's date is greater than June 14, 2021, then add all the columns across from 5Apr to 14Jun.
 (To be written in the false argument of the third if.) So on and so forth...
The [planned] column would pretty much follow similar logic but add the columns differently.
I know that is incredibly convoluted, but that's the easiest way I can explain it. I hope that helps.

Thank you so much Cody. I understand what you are saying but, not good with formulas. (at least to write this one) I would need to copy and do the same thing for Q3 and Q4. Is there a way to have someone help me write that formula from the team?
Help Article Resources
Categories
Check out the Formula Handbook template!