If the sheet shows the students in column A, and the dates in the rest of the columns, and 1 is typed when the student attends to a class and a empty cell shows the absense.
How Can I be warned Through a formula, if the student has been absent 2 times in a row?
I have used a smart tracker which only calculates the cells if the date is before or on the =today() date.
I hope someone Can help
Ok, I have a possible solution for you. Here is the basic idea - on each row create a second block of columns containing zeros and ones, but this is based on a formula that checks for successive 1's in the original dates block. If C2 and D2 are in the dates block of data, the formula in this second block would look like this -
=IF(AND(C2=1,D2=1), 1,0)
You need to drag this formula across to have one check per date pair. Note that the columns are not fixed in this formula, so they should change as you copy them to new cells. You should end up with a block of data that only has a 1 in it if there were two days in a row off.
Then all your "warning" cell has to do is sum up this new block, and if it is more than zero, they have had two days off in a row (or more). In my test sheet this second data block was in columns G:I, but yours will be different.
=IF(SUM(G2:I2)>0, "Warning","")
That is fairly straightforward, but does use a fair number of columns and formulas. These are easy to drag-copy out though, so you don't have to re-write them. It is all dynamic, and you could even modify the warning to show the count of how many back to back days off they have had. I hope I have made this clear?!
