countifs formula error while count data from date and time

[saravana]

Hi Sir,

i need to count the value between 00:00 to 08:00 (IST or EST) by formula
i can able to count the value while am copy paste the same value from notepad
but i cant able to count the value in forumla used columns

i have used formula =COUNTIFS(H:H,">=00:00",H:H,"<=07:59") its worked in copy paste column, but its not worked in formula column

please do a needful

 

[Sudhir Prabhu]

Hello Saravana,
It is because the two columns are having different data. Column D has date and time - (place a cursor on one of the cells in D col in your original version to see content). I did a text to column conversion to separate date and time; and then reapply the same (your) formula - and it works.

Note: Then again, you will observe the two data (E and H) are not same. The H col is rounded to nearest minute; whereas the E col has raw data with seconds. Hence the end results (count) are different.
Trust this helps.

Regards,
Sudhir

 

[saravana]

Hi Sudhir,

Thank you so much for your help.