Count Cell between Time Range

[bhushanmorwal1311]

Hi All,


Can somebody tell me that how can i count the cell if i've time range in entire row. Like

1:00 am

1:01 am

1:10 am

1:13 am........

2:00 am

2:25 am


So i want to know how many cell in between 1:00 am to 2:00 am.


i would really thankful to you.....if is it in VBA it would be great.

 

[Debraj]

Hi Bhusan,


Please use the below formula

=SUMPRODUCT(--(A1:A24>=TIMEVALUE("1:00 AM")),--(A1:A24<TIMEVALUE("2:00 AM")))


Regards,

Deb,


PS: but why do you need the same in VBA..

[pre][code]Function InTimeFrame(rng As Range, startTime, endTime)
Dim cell As Range
InTimeFrame = 0
For Each cell In rng
If cell >= startTime And cell < endTime Then
InTimeFrame = InTimeFrame + 1
End If
Next cell
End Function

[/pre]
To decide , use formula as

=InTimeFrame(A1:A24,TIMEVALUE("1:00 PM&#34,TIMEVALUE("2:00 PM&#34)[/code]

 

[bhushanmorwal1311]

Hi, Debraj,


Thank you so much, i would never think about "sumproduct". I thought it would be solve only count,countif, countifs...!


Thanks again....


And I need the same in vba because i want to make vba code which will reflect all time range cell count in "msg box"


----------------------------------------------

- 1:00 am to 2:00 am = 10 cells -

- 2:00 am to 3:00 am = 20 cells -

- 3:00 am to 4:00 am = 25 cells -

----------------------------------------------


something like that......