Announcement

Collapse
No announcement yet.

Countifs miscalculates date and time criteria

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

  • udhaya_k
    started a topic Countifs miscalculates date and time criteria

    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

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

    Leave a comment:


  • udhaya_k
    replied
    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.

    Leave a comment:


  • AliGW
    replied
    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.

    Leave a comment:


  • udhaya_k
    replied
    Wow! I just noticed those values. Your statement is true. Could you educate me how it comes or how can I resolve this issue?

    Leave a comment:


  • AliGW
    replied
    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.

    Leave a comment:


  • udhaya_k
    replied
    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

    Leave a comment:


  • AliGW
    replied
    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.

    Leave a comment:


  • udhaya_k
    replied
    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

    Leave a comment:


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

    Leave a comment:

Working...
X