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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies