How to perform a formula on a specific date, then stop performing it after that date.

[Lyanis_K]

Hi all. New here but in desperate need to know if this is possible.

I'm trying to figure out if there's a way to perform a formula only on a specific date, and once the date has passed, it'll keep the results from when it was performed, so that the results don't continue to change after the day has passed.

Context: I track sales performance on a daily bases. We have monthly goals that are divided into daily goals (a formula that takes the monthly goal and divides it amongst operating hours. There's a specific chart for this.)  Lets say we don't hit our daily goal for a few days, whatever we are short will get distributed into the remaining days of the month. This is determined by a short/over cell. So lets say as of todays date we are short, I want it to perform the formula to calculate the daily goal and add what we are short, divided by number of days remaining.  But I don't want it to continue performing this formula for that cell after today, otherwise the goals for previous days would continue to change as our results change.

Is this something that's possible?

Any help would be greatly appreciated!

[#2 · Accepted solution]

@Lyanis_K 

Does the attached workbook give you something to work with?

[#3]

@Hans Vogelaar It certainly gives me a starting point! thank you. Question though, and forgive me for being such a beginner, but in the formula where it says if b2="", "" what do the "" refer to? what is it saying exactly? 

[#4]

@Lyanis_K 

If the Actual sales amount in column B is "" (empty), we don't want to return an Over/Short amount, so the formula returns an empty string "". Otherwise, it calculates the Over/Short amount based on the Actual amounts up to and including that day.