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).