• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting cells on the basis of date and time-Hourly

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
 

Attachments

Peter Bartholomew

Well-Known Member
@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.
 

Attachments

Last edited:
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

Well-Known Member
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.
 

Attachments

Top