Jun 03 2019 06:21 AM
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.
Jun 03 2019 06:36 AM
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
Jun 03 2019 06:38 AM
Input Data has been extracted from Biometric Machine. You can check this as an example.
21-May-2019 | 22-May-2019 | 23-May-2019 | 24-May-2019 | 25-May-2019 | 26-May-2019 | 27-May-2019 | 28-May-2019 | 29-May-2019 | 30-May-2019 | 31-May-2019 |
Weekly Off | Absent | 10:38 | 10:25 | 10:35 | 10:28 | 10:41 | Weekly Off | 10:31 | 10:45 | 10:30 |
21:05 | 21:25 | 21:06 | 21:10 | 21:07 | 21:03 | 21:06 | 21:08 |
Jun 03 2019 06:43 AM
@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?
Jun 03 2019 07:19 AM
Hii Bhushan, i have attached herewith the file. Please help me out & you can call me up on + 91 8171232221 @bhushanz
Jun 03 2019 11:44 PM
@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.
Jun 04 2019 02:48 AM
@bhushanz , just in case - that's not enough. In other rows are also texts, even without ', which shall be converted to numbers (aka time).