• 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

  • count hourly subject to date and time.xlsx
    9.9 KB · Views: 6
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.
 

Attachments

  • count hourly subject to date and time.xlsx
    17.8 KB · Views: 7
@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

  • count hourly subject to date and time (PB).xlsx
    18 KB · Views: 7
Last edited:
This version also has COUNTIFS with a helper range
= COUNTIFS( dateTime, ">="&(date+time), dateTime, "<"&(date+time+oneHour))
 

Attachments

  • count hourly subject to date and time (PB).xlsx
    18 KB · Views: 13
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
 
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

  • count hourly subject to date and time (PB) (3).xlsx
    20.1 KB · Views: 5
Back
Top