CODEWITHSUNDEEP
excelexcel-formula
Karan Kashyap
Karan Kashyap

Reputation: 39

Use comparison operator with time field in excel

I have a column time in my Excel sheet which has values like

5:20PM
7:00PM
3:30AM
...etc

Now I wanted to use a CountIf function to count those cells whose time does not come under the range 6am to 5pm. Basically any 'Time' which is either less than 6am and greater than 5Pm should be counted. However, I could not make it work, is there a function that I can use in this scenario?

Upvotes: 0

Views: 245

Answers (1)

user10917479
user10917479

Reputation:

I believe the function you are looking for is TIME(hour, minute, second).

Make sure the Time column is of Excel format Time. Then your CountIf criteria can be ">"&TIME(19,0,0)for greater than 5:00PM or "<"&TIME(6,0,0) for less than 6:00AM.

You also want an or criteria overall. So you can add two CountIf functions together, one for each criteria, or make a dummy column and sum over that.

=COUNTIFS(datarange,">"&TIME(17,0,0))+COUNTIFS(datarange,"<"&TIME(6,0,0))

Upvotes: 2

Related Questions