Forum Discussion

shashank1234's avatar
shashank1234
Copper Contributor
Jun 03, 2019

Formula not working properly...

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...
Formulas and Functions
shashank1234's avatar
shashank1234
Copper Contributor
Jun 03, 2019

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 

bhushanz's avatar
bhushanz
Copper Contributor
Jun 03, 2019

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?

    • bhushanz's avatar
      bhushanz
      Copper Contributor
      Jun 04, 2019

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 04, 2019

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

Resources