Announcement

Collapse
No announcement yet.

Countifs miscalculates date and time criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Countifs miscalculates date and time criteria



    My criteria is count if date time is between 8/10/2018 6:17:00 PM and 8/10/2018 6:17:56 PM and name matches a cell value.

    Problem:
    If a record has exact date time of 'Greater than or Equal to' (<=) criteria, here=8/10/2018 6:17:56, it does not calculate. It does calculate if I change date time criteria '8/10/2018 6:17:56' to '8/10/2018 6:17:57'.
    Attached Files
    Defeat Easy, Win Hard

  • #2
    Sorry - I don't understand the problem. What is wrong with the results in your test workbook?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    Comment


    • #3
      Hi Ali,

      Sorry. Yesterday the formula result in E5 (in the attached file) showed only 2 when the date was 8/10/2018 18:17:55. Today I opened and it shows correct count.

      BUT, the problem persists in the original file. I have attached new excel file with problem columns and screenshots.

      Top of screenshot.jpg ensures that there is only one name = TEST.

      Bottom of screenshot.jpg shows the result of countif = 361.

      COUNTIF DATETIME ISSUE 2.xlsx proves how many TEST are there with the date time criteria = 362.

      Now I don't know where the problem occurs.
      Attached Files
      Defeat Easy, Win Hard

      Comment


      • #4
        The screenshot is too small to read - sorry. The attached file is NOT the problem file, so it is of no use - it shows 362, as you expect.

        Ali

        Enthusiastic self-taught user of MS Excel who's always learning!
        If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

        Comment


        • #5
          Hi Ali,

          Sorry about screenshot issue. I've attached once again maximized. But for excel file if I change D5 value in Sheet2 from 8/10/2018 18:48:02 to 8/10/2018 18:48:01 then the problem arise.

          And 362 records could be checked by 'Compare' sheet.

          Please let me know if it needs to be some more elaborate.

          Thank you
          Attached Files
          Defeat Easy, Win Hard

          Comment


          • #6
            When each of the two cells are set to 8/10/2018 18:48:01,

            A363 on the compare sheet has this value: 43322.7833455558

            D5 on sheet 2 has this value: 43322.7833449074

            They are not the same. A363 is several milliseconds larger.
            Ali

            Enthusiastic self-taught user of MS Excel who's always learning!
            If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

            Comment


            • #7
              Wow! I just noticed those values. Your statement is true. Could you educate me how it comes or how can I resolve this issue?
              Defeat Easy, Win Hard

              Comment


              • #8
                Well, the first thing I would want to work out is why your source data is coming in that way. I have a feeling you might have to work on the imported data and make sure that it is rounded to full seconds.
                Ali

                Enthusiastic self-taught user of MS Excel who's always learning!
                If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

                Comment


                • #9
                  Hi Ali,

                  Sorry. It was my mistake. I used this number '0.0000115740695036948' which is for 1 second like time + (randbetween(5,7) * this number). I generated the date time manually like this to test the formula. Now I realise that if I work on the imported data (or just TIME function) I would have got the correct result.

                  Thank you for your assistance and apologize for misdirecting you.
                  Defeat Easy, Win Hard

                  Comment


                  • #10


                    That's fine - as long as your issue is resolved, it was worth asking.
                    Ali

                    Enthusiastic self-taught user of MS Excel who's always learning!
                    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

                    Comment

                    Working...
                    X