Counting cells on the basis of date and time-Hourly

[amit_gupta123]

Dear All

I am looking forward for a solution wherein the hourly productivity can be calculated on the basis of date and time. Date and time is in column A. Time is in 24 hours format. I tried countifs along with wild character but no luck

Could you please suggest. I am attaching the sample data

Regards

 

[vletm]

amit_gupta123
Your A-column values has also three digits something ... hmm?
This has different output, but it would be one possible to get something that You're looking for.

 

[amit_gupta123]

Thanks ,.....It is helpful

 

[Peter Bartholomew]

@amit_gupta123
The problem I encountered with your data was that column A is comprised of text strings rather than date-times. Hence COUNTIFS will fail.
= COUNT( IF( (--createdDate>= date+time) * (--createdDate< date+time+oneHour), 1 ) )
where 'oneHour' refers to
=TIME(1,0,0)
and date and time are relative references to single cells
I would also observe that the first hour after midnight is double counted.

 

[Peter Bartholomew]

This version also has COUNTIFS with a helper range
= COUNTIFS( dateTime, ">="&(date+time), dateTime, "<"&(date+time+oneHour))

 

[amit_gupta123]

Thanks....the array formula without a helper column is working very fine....You are a rockstar

 

[amit_gupta123]

Hi Peter

My Bad...i didn't give a thought that data is growing immensely, hence array formula would enhance the time. Can you share the helper column resolution as the sheet attached is for array formula

Regards

 

[Peter Bartholomew]

Have you encountered a requirement for speed optimisation? How long does the calculation take?
The attached uses helper ranges and SUMIFS which should be an improvement of 3x or there about.
If you have calculations that are never going to change because they are for past years, the values could be 'hard-wired' so there is no recalculation.