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

    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