Formula not working properly...

Copper Contributor

I am working on staff attendance management with excel. I am using Biometric to get Punch in data & Punch out data. And this Biometric converts all data into excel.

 

Now i am applying this formula:

 

=COUNTIFS(D3:AH3, ">10:31", D3:AH3, "<=10:45") to understand & get no. of days this person is coming between 10:31 HRS to 10:45 HRS. But Result is showing ZERo. 

 

May be because of Formating of Data. Could you help me with this.

6 Replies

I feel there is some issue with the input data.
Because when I enter the data in this format it works perfectly.
Format: "HH:MM AM"

e.g. 10:32 AM

Input Data has been extracted from Biometric Machine. You can check this as an example. 

 

 

21-May-201922-May-201923-May-201924-May-201925-May-201926-May-201927-May-201928-May-201929-May-201930-May-201931-May-2019
Weekly OffAbsent10:3810:2510:3510:2810:41Weekly Off10:3110:4510:30
  21:0521:2521:0621:1021:07 21:0321:0621:08

@bhushanz 

@shashank1234 when I copy your data in my sheet and try, it works.
Just check couple of things:
Excel file type is: xls / xlsx

Cell Format: Time (h:mm, h:mm AM/PM)

if still does not work, can u pls attach the file here?

Hii Bhushan, i have attached herewith the file. Please help me out & you can call me up on + 91 8171232221 @bhushanz 

@shashank1234 found the issue.
You have an extra tick - ' before the time, example: '10:32

So just remove ticks '

Refer attached file, I have cleared Row #3 and correct value is in #AJ3. Please check.

@bhushanz , just in case - that's not enough. In other rows are also texts, even without ', which shall be converted to numbers (aka time).