Home

Formula not working properly...

%3CLINGO-SUB%20id%3D%22lingo-sub-664753%22%20slang%3D%22en-US%22%3EFormula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664753%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20staff%20attendance%20management%20with%20excel.%20I%20am%20using%20Biometric%20to%20get%20Punch%20in%20data%20%26amp%3B%20Punch%20out%20data.%20And%20this%20Biometric%20converts%20all%20data%20into%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20i%20am%20applying%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(D3%3AAH3%2C%20%22%26gt%3B10%3A31%22%2C%20D3%3AAH3%2C%20%22%26lt%3B%3D10%3A45%22)%20to%20understand%20%26amp%3B%20get%20no.%20of%20days%20this%20person%20is%20coming%20between%2010%3A31%20HRS%20to%2010%3A45%20HRS.%20But%20Result%20is%20showing%20ZERo.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMay%20be%20because%20of%20Formating%20of%20Data.%20Could%20you%20help%20me%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-664753%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664785%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664785%22%20slang%3D%22en-US%22%3E%3CP%3EI%20feel%20there%20is%20some%20issue%20with%20the%20input%20data.%3CBR%20%2F%3EBecause%20when%20I%20enter%20the%20data%20in%20this%20format%20it%20works%20perfectly.%3CBR%20%2F%3EFormat%3A%20%22HH%3AMM%20AM%22%3C%2FP%3E%3CP%3Ee.g.%2010%3A32%20AM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664787%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664787%22%20slang%3D%22en-US%22%3E%3CP%3EInput%20Data%20has%20been%20extracted%20from%20Biometric%20Machine.%20You%20can%20check%20this%20as%20an%20example.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E21-May-2019%3C%2FTD%3E%3CTD%3E22-May-2019%3C%2FTD%3E%3CTD%3E23-May-2019%3C%2FTD%3E%3CTD%3E24-May-2019%3C%2FTD%3E%3CTD%3E25-May-2019%3C%2FTD%3E%3CTD%3E26-May-2019%3C%2FTD%3E%3CTD%3E27-May-2019%3C%2FTD%3E%3CTD%3E28-May-2019%3C%2FTD%3E%3CTD%3E29-May-2019%3C%2FTD%3E%3CTD%3E30-May-2019%3C%2FTD%3E%3CTD%3E31-May-2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EWeekly%20Off%3C%2FTD%3E%3CTD%3EAbsent%3C%2FTD%3E%3CTD%3E10%3A38%3C%2FTD%3E%3CTD%3E10%3A25%3C%2FTD%3E%3CTD%3E10%3A35%3C%2FTD%3E%3CTD%3E10%3A28%3C%2FTD%3E%3CTD%3E10%3A41%3C%2FTD%3E%3CTD%3EWeekly%20Off%3C%2FTD%3E%3CTD%3E10%3A31%3C%2FTD%3E%3CTD%3E10%3A45%3C%2FTD%3E%3CTD%3E10%3A30%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E21%3A05%3C%2FTD%3E%3CTD%3E21%3A25%3C%2FTD%3E%3CTD%3E21%3A06%3C%2FTD%3E%3CTD%3E21%3A10%3C%2FTD%3E%3CTD%3E21%3A07%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E21%3A03%3C%2FTD%3E%3CTD%3E21%3A06%3C%2FTD%3E%3CTD%3E21%3A08%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353690%22%20target%3D%22_blank%22%3E%40bhushanz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664789%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353680%22%20target%3D%22_blank%22%3E%40shashank1234%3C%2FA%3E%26nbsp%3Bwhen%20I%20copy%20your%20data%20in%20my%20sheet%20and%20try%2C%20it%20works.%3CBR%20%2F%3EJust%20check%20couple%20of%20things%3A%3CBR%20%2F%3EExcel%20file%20type%20is%3A%20xls%20%2F%20xlsx%3C%2FP%3E%3CP%3ECell%20Format%3A%20Time%20(h%3Amm%2C%20h%3Amm%20AM%2FPM)%3CBR%20%2F%3E%3CBR%20%2F%3Eif%20still%20does%20not%20work%2C%20can%20u%20pls%20attach%20the%20file%20here%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-664972%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-664972%22%20slang%3D%22en-US%22%3E%3CP%3EHii%20Bhushan%2C%20i%20have%20attached%20herewith%20the%20file.%20Please%20help%20me%20out%20%26amp%3B%20you%20can%20call%20me%20up%20on%20%2B%2091%208171232221%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353690%22%20target%3D%22_blank%22%3E%40bhushanz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666365%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353680%22%20target%3D%22_blank%22%3E%40shashank1234%3C%2FA%3E%26nbsp%3Bfound%20the%20issue.%3CBR%20%2F%3EYou%20have%20an%20extra%20tick%20-%20'%20before%20the%20time%2C%20example%3A%20'10%3A32%3C%2FP%3E%3CP%3ESo%20just%20remove%20ticks%20'%3C%2FP%3E%3CP%3ERefer%20attached%20file%2C%20I%20have%20cleared%20Row%20%233%20and%20correct%20value%20is%20in%20%23AJ3.%20Please%20check.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666750%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20not%20working%20properly...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F353690%22%20target%3D%22_blank%22%3E%40bhushanz%3C%2FA%3E%26nbsp%3B%2C%20just%20in%20case%20-%20that's%20not%20enough.%20In%20other%20rows%20are%20also%20texts%2C%20even%20without%20'%2C%20which%20shall%20be%20converted%20to%20numbers%20(aka%20time).%3C%2FP%3E%3C%2FLINGO-BODY%3E
shashank1234
New 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).

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies