The COUNTIF function with time periods:
At the request of Sus and Greet, I will show you how to count the number of time periods.

I guess, when you have a lot of staff here and there and a few arrive late to work, you, as an employer, will not like it.
We can, as you all know, use conditional formatting in the twinkling of an eye but, we can also count.
I will explain how to do this.

We will start with the “Conditional Formatting” option so that those who’ve came too late will have an idea.
I suppose that everybody knows how to use it but, for clarity’s sake, I will explain this.

Just click the “Conditional formatting” button in the “Ribbon” and select “New Rule”.
In the dialog box that appears, select the “Format only cells that contain” (1) type.
In the first text box, select “Cell Value” (2).
In the second text box, select “Greater Than” (3).
And, in the third text box, type the hours (4), for eg., 8:00
Click the “Format” (5) button and set the formatting for these cells.
Click the OK button.

When we copy the conditional formatting to the underlying cells, we can clearly see who all have come late for work.
Here, it is clear how many there are, but, if you have a lot of employees, you will not be able to determine who all have come late, easily.

For this, we can use the “COUNTIF” function.
Type the equal sign (=), followed by the COUNTIF function.
Next, open the brackets and, as you can see in the image below, this function needs two arguments.
The first is the “Range”.
The second is the “Criterion”.

The range is B2: B6, in this case.
The criterion is anyone who has come after eight o’clock, in this case.
For this, we can use the “normal” time format. For example, 8:00.

So, we type a comma after entering the range.
Then, we type the double quotes.
We then type the greater than sign >.
This is followed by the hour, just as we would type it in a cell. eg 8:00.
We now close the quotation marks.
Following this, we close the brackets.
Press the Enter key on your keyboard.

This gives us the number of people who have come late.
Note that the format of the cell has to be set to “Number”.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats