I need a 183 day rolling formula

[dnoteman]

Our company is moving to a rolling 183 day attendance policy. Attached is the file in which I would like that formula to show the rolling number under each employees name. I can't even tell you all the formulas I've tried at this point and none of them are doing what I need them to do, and I may be trying to over simplify this too. Cell A3 is displaying the date that it's looking back through but really the new policy started on 6/1/2017, so on 12/1/17 is the first opportunity for any occurrences on 6/1 to fall off.

[shg]

You've given no indication of the result you want.

[Mvaldesi]

Ok, not the most glamorous solution (someone will probably offer something better,) but I simply did a SUM for a 183 day period and locked the reference, then the each day, you can click into the SUM function to highlight the range and you're able to drag it down (by the middle) to bring down the 183 day "block" by one day (don't drag by the corner which will expand the range one or more days.)

Also, I had to adjust you IF in Column D; since the value was technically a formula, it wasn't summing the formula results as expected:

=IFERROR(VALUE(IF(C167="","",IF(C167="late",".5",IF(C167="AB","1",IF(C167="NCNS","1",IF(C167="LE",".5",IF(C167="LL",".5"))))))),"")

Hope this helps or someone responds with something better!

[gilpin004]

Hello,

You are going to want to use the =SUMPRODUCT formula to do what you are attempting to do. This has been previously set up in a different structured format, please see the attached spreadsheet.

Your formula to use is
=SUMPRODUCT((D$4=$H6:$NH6)*($H$5:$NH$5>=EDATE($C$1,-$D$1))*($H$5:$NH$5<=$C$1))

[JohnTopley]

Try (If I understand correctly)

in C2

=SUMIFS(D:D,A:A,">="&TODAY()-183,A:A,"<="&TODAY())

AND change your formulae

=IF(C167="","",IF(C167="late",0.5,IF(C167="AB",1,IF(C167="NCNS",1,IF(C167="LE",0.5,IF(C167="LL",0.5))))))

remove quotes from the numeric values so they are treated as numbers not TEXT

[Logit]

.
Here is another version :

Please Login or Register  to view this content.

[dnoteman]

Thank you all. Turns out my biggest (and simplest) issue was that the points were being counted as text and not numbers. Good Grief. Thanks JohnTopley, worked like a charm.