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

## Files

Defeat Easy, Win Hard

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

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

## Files

Defeat Easy, Win Hard

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

• 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

## Images

Defeat Easy, Win Hard

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

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

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

## Participate now!

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