Countif to find if a time has been exceeded

**inayat** · 09-05-2011, 02:29 AM

Hi All,

Guess I am really not having a good day today. Here is the problem. I get a attendance report from software. Now I want to see who all have come late and who all have left early. When i use the countif formula (Sheet attached.. Yellow color) I am not able to get the desired results. I have tried to format the cells to time format also and still does not work.. Please suggest.

Thanks in Advance

**arthurbr** · 09-05-2011, 03:24 AM

Your duration are text looking like time, not real time

Enter 1 in an empty cell - Copy - Select rows 5 and 6 - Right Click - Paste spaecial - Multiply - OK

Change your formula as follows

Please Login or Register  to view this content.

**PaulG2011** · 09-05-2011, 03:29 AM

Hi inayat,

This might not be the best way to do this so an Excel guru may come along soon to give a better method, but I would do this by inserting a few hidden rows in your sheet to calculate time differences.

For example:

In cell B7 I would enter the formula: ="09:30"-B5
and then copy the formula across to all the other columns in row 7. The cell needs to be formatted as a number.

I would then insert a new row in row 8 to push your yellow highlighted cells down, and in cell B8 (which is now blank) I would enter: =B6-"18:20"

This way, "bad values" (i.e. people arriving late, or people leaving early) are always displayed as negative numbers.

Your formulae in cells in B9 and B10 with the yellow highlight now need to say something like:

=COUNTIF(B7:AF7,"<0") and
=COUNTIF(B8:AF8,"<0")

If you want to hide the values of the cells in rows 7 and 8 because the spreadsheet look ugly you can select all the cells in rows 7 and 8, go to format ---> cells ---> click custom and the bottom and enter ";;;" (without the quotes) and it will make the values disappear. If you click on the cells in rows 7 and 8 you can see the formulas are still there, it just hides their values to make your spreadsheet look nicer.

Good luck,

Paul