0

I want to count number of cells containing the value time. Eg: 05:29, 14:36, 22:05.

Here, these times are entered in indiviudal cells. And I want to count number of cells containing time. Like Time: 3. But not the time function or total time function

-Thanks

Mansoor
  • 1
  • 2

2 Answers2

2

Assuming your data is arranged like this enter image description here you can use =COUNTIF(B1:L1,">0") to count the non empty cells, and you will get then this enter image description here

If you want to check for late arrivals, use this formula = IF(Q2<>"",IF(Q2-Z2 >0,"L","N"),"") where I called Q2 the cell containing the arrival time and Z2 the cell with the expected arrival time. You will get a L for late arrivals, a N for an arrival on time.

L.Dutch
  • 926
  • 3
  • 17
  • 38
  • Here I've attached an image. Fill the cells with the header instructions. it looks complicated. [![Temp_calc.jpg](https://s22.postimg.org/rck58v6v5/Temp_calc.jpg)](https://postimg.org/image/aosn6dc3h/) – Mansoor Oct 31 '16 at 06:30
  • According to the image, "P" means present. If time "9:27", then it is late by the start time. like this will have for a month. each row is an employee record. **F2** should be no of present/p values if user came before or actual start time. **G2** denotes the Letter "L", like P for present. if entry time is 9.00 and user arrives by 15 min late, i need to count the number of 15 min late in **H2**. – Mansoor Oct 31 '16 at 06:36
  • Are Q, R and other the clock in time? – L.Dutch Oct 31 '16 at 07:30
  • Yes, they are the entry of start time for each day for every month. there the value will be **P** for _present_, **L** for _absent_, If time entered means, that is late from the starting time. Starting time can be 9:00 AM. and rest is the calculation. – Mansoor Oct 31 '16 at 07:55
  • check the edit to my answer. I think you can start from there. – L.Dutch Oct 31 '16 at 08:01
  • Thanks L.Dutch. That works, But actually I made Like this '= IF(D7<>"",IF(D7-Q7>=0,"P",TEXT(Q7-D7,"H:mm")),"")' , that is , if the user is late, then remaining time should be displayed. Start time:**09:00** and user arrives at **09:25**, then it shows **00:25** min. After this, how I've to add this for all days and to calculate the no of days **Present/"P"** and no of mins delayed on each day. – Mansoor Oct 31 '16 at 08:29
  • Here, I'll add this. I'll enter P, L or the time delay as 09:18 am, then it has to calculate the no of P, no of L and the time delays upto 00:15 min, 00:30 min and 1 hour from starting time. – Mansoor Oct 31 '16 at 09:16
0

Another solution along the same lines as L.Dutch is to use the IsNumber() function of excel. It requires two steps. First using that function and then summing the TRUE values. The one advantage of this method is that it makes it more explicit as to what it will add up.

Thought I'd provide this in case it is useful, but I would use L. Dutch's answer first.

rohrl77
  • 3,277
  • 11
  • 47
  • 73