Formula help

Kelly Miller

Quarter 2 (April- June) I need the formula that does the following:

Take the week of 5-Apr + 12-Apr +Apr-19 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 28-Jun. 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.

Cody Holmes

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 date-name column—in your case, June 28 because your last date-name column is "28-Jun". If today's date is greater than that date-name 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 5-Apr to 28-Jun.
• (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 5-Apr to 21-Jun.
• (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 5-Apr to 14-Jun.
• (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.

Cody Holmes

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 date-name column—in your case, June 28 because your last date-name column is "28-Jun". If today's date is greater than that date-name 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 5-Apr to 28-Jun.
• (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 5-Apr to 21-Jun.
• (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 5-Apr to 14-Jun.
• (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.

Kelly Miller

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?