Trying to count if time falls between two times using a time generated from Text function

[Theneoalchemist]

Hi everyone,

I have been searching for a way to have Excel count number of entries between two times from a data set.

The issue is that the data set I receive has column "A" set as "mm,dd,yyyy HH:MM". So I created a different column ( "I" ) that is pulling the time utilizing =If(A2="","",Text(A2,"hh:mm")). Then I have another cell that has the function =Countifs(I:I,">=00:00",I:I,"<=06:00"). The problem is that it keeps returning a result of 0. If I go into the I column and manually type a time, for instance "06:43" then the counter will increase to 1.

Is there something else that I need to be doing?

 

[sandy666]

try with: =If(A2="","",--Text(A2,"hh:mm")) and set format Time for Time column

 

[Theneoalchemist]

try with: =If(A2="","",--Text(A2,"hh:mm")) and set format Time for Time column

That worked, Thank you!.

I am learning Excel still, could you explain to me what adding "--" does to the function?

 

[azad092]

Hi everyone,

I have been searching for a way to have Excel count number of entries between two times from a data set.

The issue is that the data set I receive has column "A" set as "mm,dd,yyyy HH:MM". So I created a different column ( "I" ) that is pulling the time utilizing =If(A2="","",Text(A2,"hh:mm")). Then I have another cell that has the function =Countifs(I:I,">=00:00",I:I,"<=06:00"). The problem is that it keeps returning a result of 0. If I go into the I column and manually type a time, for instance "06:43" then the counter will increase to 1.

Is there something else that I need to be doing?

hi
what you mean by "count number of entries between two times" you mean count number of entries during a specific time or something else... explaine and if possible for you attach the excel file

 

[sandy666]

"--"

-- <=> 1* <=> 0+
in short changes text to a number

 

[Theneoalchemist]

-- <=> 1* <=> 0+
in short changes text to a number

Interesting. Thank you for the knowledge. I will probably be using that trick moving forward.

 

[sandy666]

You are welcome
Thanks for the feedback ?

 

[jasonb75]

Given that times and dates are stored as numeric values, you could use numeric functions to split them instead of coercing text.

Time only =IF(A2="","",MOD(A2,1))
Date only =IF(A2="","",INT(A2))

Text coercion requires 2 additional calculations per non-blank cell.