Possible to ignore column until a certain date?

[Formulation]

Is there a way to ignore a column until a certain date arrives?

For example, I have some columns splits up in Quarters.
[TABLE="width: 400"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Q2 17'[/TD]
[TD="align: center"]Q3 17'[/TD]
[TD="align: center"]Q4 17'[/TD]
[TD="align: center"]Q1 18'[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]

Using the guide below, Q4 17' and Q18' need to be ignored until the first day in the next quarter
So, for the cell beneath Q4 17' to be enabled or referenced, the current date would have to be January 1st 2018 (the first day in Q1 18')
(Q1): January 1 - March 31.
(Q2): April 1 - June 30.
(Q3): July 1 - September 30.
(Q4): October 1 - December 31.

Finding a solution to that issue is my main problem.

Also, would there be a way to ignore the Q2 17' column once the first day of Q3 18' was reached (July 1st)

Lastly, once a column, say Q3 17', is finished (Oct 1st). Is there a way to only use the contents of that cell if the contents of column F are above 0?

 

[Formulation]

https://i.imgur.com/yQRSc8X.jpg
I'll be happy to link a sample to the sheet if anyone knows a trusted hosting site or online source that enables editing of formulas.


This is an attendance spreadsheet that I'm having trouble accurately totaling accrued points (absences/being late) in combination with a point reduction for perfect attendance (blank cells) for each quarter (1/1 - 3/31, 4/1 - 6/30, etc)


We have a quarterly perfect attendance system where if all cells are blank in that quarter, you receive a 1 point reduction in your total. If all cells in range T6:DG6 (range of dates containing Q4) are blank, column H (Q4 16') will have a 1. If there's any value in the cell, it will be blank.


If you have 0 points going into any given Quarter, lets say Q1 (Jan 1st - March 31st)... That quarter needs to be ignored for that employee (row). If you're at 0 points total, the next quarter you would be qualified to receive a perfect attendance 1 point reduction, would be the quarter following your next point received.


I also need to ignore the current Quarter column and future quarter columns until the quarter is finished. example: (Q4): October 1 - December 31 columns cell contents for each employee would be ignored.
If someones date of hire (column A) is say 9/1/2017, the perfect attendance quarter columns up to that point including the current quarter 9/1/2017 is in, need to be ignored.


Currently columns E/F/G account for the date of hire and ignore any cells before column A's date using the following formula, but it doesn't account for the perfect att. columns. code below =ADDRESS(ROW(),MAX(10,IFERROR(MATCH(TODAY()-365,A$5:SU$5,0),FALSE),IFERROR(MATCH(A6,A$5:SU$5,0),FALSE)))


I have a total column for all points (without taking into account perfect attendance) A second column that counts up the valid perfect attendance quarters. Then a third that will subtract the 2nd from the 1st for a final total.


Yeah, it's a lot of information but hopefully that creates a clear picture of what I'm trying to accomplish. Any other options or ways to go about this would be helpful but this is the cleanest way I've came up with after two months.

 

[Formulation]

I may be getting closer on one of the issues of ignoring columns I-Q unless a date has reached by using the following formula.

This is testing on column N (Q1 18')

=COUNTIFS(UU5,”=”&TODAY(),UU5,”=”&TODAY())

I need to incorporate this formula with it somehow:

=IF(COUNTA(RI8:UT8)=0,"1","")

Basically if the range RI:UT8 has blank cells, cell N8 will have a 1 populated. That 1 in N8 will then be able to be referenced, then subtracted from cell H8 only if cell UU5 (the first day of Q2 18') has been reached. I still need it to be subtracted for (today()+365 if that's possible

hmm

 

[Formulation]

I've simplified the problem, ignore previous postings:

[FONT=&quot]New sample image including columns: https://imgur.com/bfakfLP[/FONT]
[FONT=&quot]This is an attendance spreadsheet that I'm having trouble accurately totaling accrued points (absences/being late) in combination with a point reduction for perfect attendance (blank cells) for each quarter (1/1 - 3/31, 4/1 - 6/30, etc)[/FONT]
[FONT=&quot]T5, U5, V5, etc... is the start of dates[/FONT]
[FONT=&quot]This should simplify my confusing original post: I'll be using Row 6 as an example.[/FONT]
[FONT=&quot]The total points in column H6 needs to take into account the totals in columns E, F, and G. Then subtract what is in I6-Q6.[/FONT]
[FONT=&quot]T6 (10/1/16) = CO (1 point) but needs to be ignored due to being 365+ days in the past (working)[/FONT]
[FONT=&quot]X6 (10/5/16) = CO (1 point)[/FONT]
[FONT=&quot]KG6 (7/1/17) = CO (1 point)[/FONT]
[FONT=&quot]Y6:KF6 (10/6/17 - 6/30/17) = blank cells[/FONT]
[FONT=&quot]Y6:KF6 contains two complete quarters of blank cells[/FONT]
[FONT=&quot]Q1 (1/1/2017 - 3/31/17) (DH6:GS6) and[/FONT]
[FONT=&quot]Q2 (4/1/17-6/30/17 (GT6:KF6)[/FONT]
[FONT=&quot]*Q4 16' (i6) would be be blank or 0 since the full Q4 period 10/1/16 - 12/31/16 (T6:DG6) was not empty.[/FONT]
[FONT=&quot]So the row 6 final total (H6) would equal 1 point instead of 2 it currently shows. T6 Point = ignored X6 Point = counted (current total 1 point) Entire Q1 blank = Minus 1 point (current total 0 points) Entire Q2 blank = Ignored due to total at that point being 0 points) KG6 point = counted (total now at 1 point)[/FONT]
[FONT=&quot]I also need to ignore the current Quarter column and future quarter columns until the quarter is finished. example: (Q4): October 1 - December 31 columns cell contents for each employee would be ignored. If someones date of hire (column A) is say 9/1/2017, the perfect attendance quarter columns up to that point including the current quarter 9/1/2017 is in, need to be ignored.[/FONT]
[FONT=&quot]Yeah, it's a lot of information but hopefully that creates a clear picture of what I'm trying to accomplish. Any other options or ways to go about this would be helpful but this is the cleanest way I've came up with after two months.[/FONT]

 

[Formulation]

Progress from a reddit post if anyone wants to take a dive into the confusion

So row 6 has three "CO"'s in it, but why is there a 2 in E6 for "Call Off"? If it's because you're ignoring the T6 entry, that's good, but then why tell us we need to ignore it? We don't - you already have that working. Think of your problem as a black box. You have inputs, you want a box that provides outputs. Same with the blanks in I:Q - is that working already? If so, we don't care why they're blank, just that they are blank.
So assuming nothing is working: for E6, calculate the number of Call Off's that matter.

=SUMPRODUCT(($T$5:$AFI$5>EDATE(TODAY(),-12))*($T$5:$AFI$5<=TODAY())*($T6:$AFI6<>""))

Do the same for Tardy and Leave Early.
For I:J, I don't know what you want if there is perfect attendance. But this will put a blank if there's any values for the quarter and a 1 otherwise

=IF(SUMPRODUCT(($T$5:$AFI$5>=DATE(LEFT(RIGHT(I$5,3),2)+2000,MID(I$5,2,1)*3-2,1))*($T$5:$AFI$5<EDATE(DATE(LEFT(RIGHT(I$5,3),2)+2000,MID(I$5,2,1)*3-2,1),3))*(LEN($T6:$AFI6)>0))>0,"",1)

Maybe for the last formula I put up, just change it so it only goes non-blank if you really want to count it. I'm getting confused on which points are good and if blank is good or bad, but add a condition with * to check the quarter before and use that to see if you really want the cell to be blank or not. Then it's just an easy SUM.

REPLY Yes, the T6 entry is ignored. I just added that in there in case the solution would handle it easier.
I've made some progress with your formulas. My current solution is to use this in R6.

=SUM(I6,J6,K6,L6)

Basically I'm avoiding adding cells M6-Q6 because the full quarter date range has not been completed.
This works, although it would require manual updating the above formula in R6 as the next quarters are completed.

I'm using your second formula in I-Q columns, which works.

=IF(SUMPRODUCT(($T$5:$AFI$5>=DATE(LEFT(RIGHT(I$5,3),2)+2000,MID(I$5,2,1)*3-2,1))*($T$5:$AFI$5<EDATE(DATE(LEFT(RIGHT(I$5,3),2)+2000,MID(I$5,2,1)*3-2,1),3))*(LEN($T6:$AFI6)>0))>0,"",1)

However, the main problem is...
Q4 16' = 1 point was received
Q1 17' = 0 points were received (J6 = 1) would bring total (H6) to 0 points
Q2 17' = 0 points were received (K6 = 1) would either need to be ignored (as H6 can't go below 0) or somehow reference H6 at the end of Q1 17' and if H6 is 0 at that point, the only valid value in K6 would be blank (to avoid having the total in H6 going to 0 instead of 1).

and if this was solved I could live with manual updating column R's formula as the quarters are completed.

The only thing I can think of is adding a set of helper columns that display the total points after each quarter.
So using the same example, if at the end of Q1 17' total is 0, due to subtracting the 1 point back for perfect attendance.
The Q1 17' helper column would be empty.
Then Q2 17' would by default always have to be 0 or blank, if the previous quarters helper column total is 0.

Confused yet? I'm two months in but I think I'm onto something.. what do you think?