Perform calculation only when a certain date is reached

**Juleskor** · 05-07-2013, 08:16 PM

Hi,

I currently have a formula in F12 that sums cells F4:F11
In F13 I have a formula that checks if any cell between F4:F11 are blank, it won't calculate F12-30, and will leave the figure as 0

=IF(COUNTBLANK(F4:F11)>0,0,SUM(F12-30))

Essentially, the -30 is only calculated on the date at F1 (in this case 11/05/2013 Australian format as DD/MM/YYY)

If there a way that I can keep the above formula, but also add in a date requirement?

Example: IF F4:F11 are blank, AND TODAY()<F1, display 0, else sum F12-30

I have tried =IF(AND(COUNTBLANK(F4:F11)>0,TODAY()<F1,0,SUM(F12-30))) but I get a "#N/A - wrong number of arguments to IF" error

Kindes regards

**Pete_UK** · 05-07-2013, 09:14 PM

You had a bracket in the wrong place. Also, you don't really need the SUM function. Try this:

=IF(AND(COUNTBLANK(F4:F11)>0,TODAY()<F1),0,F12-30)

Hope this helps.

Pete

**Juleskor** · 05-07-2013, 10:32 PM

Hi Pete_UK,
Thanks for the quick response. I placed the formula in F13, but it appears to have had no affect.
I have some hidden cells at rows 16 to 23 that calculate the actual monetary values. The rows at 4 to 11 display the date that money was paid and I'm not sure if this is throwing the calculations.
I have attached the spreadsheet if this will help. There are monetary values in there, but they're not of a sensitive nature.
Basically, a sum of $6 is paid each week by 8 players and if all 8 players pay in that week, the total at row 12 is $6x8=$48. I get them to pay 5 weeks worth at a time so I don't have to ask them each week to pay.
Every week I have to subtract $30 for the cost of the score sheet, so $48-$30 = $18. Then to get the running total I sum the previous week's value at row 14 with the current week's value at row 13.
This works fine until some players forget to pay a week or are injured in that week and their $6 carries over to the next week. The totals don't reflect the true running total so that's why I would like the $30 not to be calculated until the date at row 1.
Hope this makes sense.
Thanks