Zero all cells before the next entry if greater then 120days

[Tazzbjs]

Ok, I am stuck. I have an attendance tracker that I am trying to add a "clean slate" into my formula. I have the following formula in D6 (down),

=IFERROR(IF(F6<=0,"",VLOOKUP(C6,'Attendance Lang'!$A$2:$C$9,3,FALSE)),"")

. So currently, D6 (down) will change to 0 at 12 months. I need to add another qualifier, that will return "0" in all cells above a new entry if it has been 120 days or more. For example, row 13 to row 14 is greater than 120 days, so rows 6 thru row 13 need to be "0". Thank-you all in advance for your help.

 

[bobaol]

in cell g6, how about =if(max($f$6:$f$14)>119,0,f6) copy formula g6 to g14. then instead of your formula pointing to f6, have it point to g6. cheers!

 

[Tazzbjs]

in cell g6, how about =if(max($f$6:$f$14)>119,0,f6) copy formula g6 to g14. then instead of your formula pointing to f6, have it point to g6. cheers!

Looks like that is just returning "0" if any of the cells are greater than 119. What I need is a bit more complicated than that. Essentially the policy is a point (column D) falls off after 12 months, so is "0". In addition to that, they are eligible for a "clean slate" if they go 4 months with out accruing another point. The clean slate then zeros "0" all points accrued before the date of the new entry.

 

[bobaol]

hi, can you paste the data here? it be a lot easier to work with than the image above. you can copy, then paste like this. this is an example from something i'm working on.

a1

two business days, not including the day it was received

m

t

w

r

f

s

n

m

t

w

r

f

s

n

=now()+2

m

x

x

x

x

=now()+2

t

x

x

x

x

=now()+2

w

x

x

x

x

=now()+4

r

x

x

x

x

x

x

=now()+4

f

x

x

x

x

x

x

=now()+3

s

x

x

x

x

x

=now()+2

n

x

x

x

x

 

[Tazzbjs]

Looks like that is just returning "0" if any of the cells are greater than 119. What I need is a bit more complicated than that. Essentially the policy is a point (column D) falls off after 12 months, so is "0". In addition to that, they are eligible for a "clean slate" if they go 4 months without accruing another point. The clean slate then zeros "0" all points accrued before the date of the new entry.

Ok, I added a formula in column G

=IF(B14-B13>119,"",F13)

that returns "0" and used several IF statements to make it work.

DATE	CATEGORY	POINT(s)	PURGE DATE
4/5/2023	D	0	4/5/2024
4/6/2023	D	0	4/6/2024
4/7/2023	D	0	4/7/2024
4/10/2023	D	0	4/10/2024
6/14/2023	D	0	6/14/2024
6/15/2023	D	0	6/15/2024
8/2/2023	G	0	8/2/2024
8/3/2023	G	0	8/3/2024
2/24/2024	D	1	2/24/2025

DATE	CATEGORY	POINT(s)	PURGE DATE	Day left to purge
45021	D	=IFERROR(IF(D7=0,0,IF(G6<=0,0,IF(F6<=0,"",VLOOKUP(C6,'Attendance Lang'!$A$2:$C$9,3,FALSE)))),"")	=IF(B6>0,B6+366,"")	=IF(B6,E6-$AA$1,"")	=IF(B7-B6>119,0,F6)
45022	D	=IFERROR(IF(D8=0,0,IF(G7<=0,0,IF(F7<=0,"",VLOOKUP(C7,'Attendance Lang'!$A$2:$C$9,3,FALSE)))),"")	=IF(B7>0,B7+366,"")	=IF(B7,E7-$AA$1,"")	=IF(B8-B7>119,0,F7)
45023	D	=IFERROR(IF(D9=0,0,IF(G8<=0,0,IF(F8<=0,"",VLOOKUP(C8,'Attendance Lang'!$A$2:$C$9,3,FALSE)))),"")	=IF(B8>0,B8+366,"")	=IF(B8,E8-$AA$1,"")	=IF(B9-B8>119,0,F8)
45026	D	=IFERROR(IF(D10=0,0,IF(G9<=0,0,IF(F9<=0,"",VLOOKUP(C9,'Attendance Lang'!$A$2:$C$9,3,FALSE)))),"")	=IF(B9>0,B9+366,"")	=IF(B9,E9-$AA$1,"")	=IF(B10-B9>119,0,F9)

 

[bobaol]

good for you. glad you figured it out. an idea for future work, instead of trying to use a huge formula in one column or one cell, break up the formula into multiple cells or columns, that way, it is much easier to see your result for each question asked in each cell. cheers!