Countifs miscalculates date and time criteria

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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

    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.

    :!:Forum Rules

    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.

    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.

    :!:Forum Rules

    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.

    :!:Forum Rules

    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

    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.

    :!:Forum Rules

    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

    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.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login