counting numbers in different time intervals

[beldin2]

I need to count records in each 8h (starting from first adequate hour) during day (6-22) and in each 0,5h (starting from first adequate hour) during night (22-6). For example if there is 21:50; 22:20; 22:25; 22:30; 22:50; 23:05; 23:15; 23:45 then the first adequate hour for night is 22:20 and from it I would have to start counting. Consequently I would have to count how many records is between 22:20 and 22:50; 22:50 and 23:20; 23:20 and 23:50.There is attached example https://rg.to/file/a5973758f63cc9f6c837303cd7162b77/example.xlsx.html solved for two days in K column. Could you please help me?

 

[Gerald Higgins]

Hi, welcome to the board.

I don't want to follow your link.

What do you mean by "first adequate hour" ?

What if a record occurs at exactly 22:50 ?
Does it get counted in 22:20 - 22:50 ?
Or does it get counted in 22:50 - 23:20 ?

What exactly should the results be for the sample data you provided ?

 

[beldin2]

What do you mean by "first adequate hour" ?

Hours occurs randomly during the day. I need to count them in intervals of 8h during the day an 0,5h during night but I can't count day 6-14 and 14-22 if there isn't any record at 6:00. If the first record during the day is 6:05 I count in intervals of: 6:05-14:05 and then 14:05-22:00. The same is with night. If the first record during night is 22:15 I will count how meny records were between 22:15- 22:45, 22:45-23:15, 23:15 and 23:45 and so on.

What if a record occurs at exactly 22:50 ?
Does it get counted in 22:20 - 22:50 ?
Or does it get counted in 22:50 - 23:20 ?

It doesn't matters. It only matters to be the same in whole sheet.

What exactly should the results be for the sample data you provided ?

The number of records in each interval.

 

[RoryA]

Please take a minute to read the forum rules, especially as regards cross-posting, and then add appropriate links here to your posts in other forums. Thank you.

 

[beldin2]

What do you mean by "first adequate hour" ?

Hours occurs randomly during the day. I need to count them in intervals of 8h during the day an 0,5h during night but I can't count day 6-14 and 14-22 if there isn't any record at 6:00. If the first record during the day is 6:05 I count in intervals of: 6:05-14:05 and then 14:05-22:00. The same is with night. If the first record during night is 22:15 I will count how meny records were between 22:15- 22:45, 22:45-23:15, 23:15 and 23:45 and so on.

What if a record occurs at exactly 22:50 ?
Does it get counted in 22:20 - 22:50 ?
Or does it get counted in 22:50 - 23:20 ?

It doesn't matters. It only matters to be the same in whole sheet.

What exactly should the results be for the sample data you provided ?

The number of records in each interval.

 

[beldin2]

Please take a minute to read the forum rules, especially as regards cross-posting, and then add appropriate links here to your posts in other forums. Thank you.

You are right this is a cross-post. I shouldn't have but I am little bit of hurry. I should have thought to share a link to original topic. The first main topic is https://www.excelforum.com/excel-fo...ting-numbers-in-different-time-intervals.html I would be very grateful to anyone helping me here or there.

 

[Gerald Higgins]

Please post the ACTUAL RESULTS that you expect to see, for the sample data you provided.

 

[beldin2]

Please post the ACTUAL RESULTS that you expect to see, for the sample data you provided.

There are results(right) for one day. Since you don't want to open excel files I have used print screen.

 

[Gerald Higgins]

On the contrary, I love opening Excel files, I've done it many times today already.

What I said was, I don't want to follow your link.

Thanks for posting some data, and some results.
What would have been more helpful would be to post some data, and the results FOR THAT DATA.
It looks like your post has cutoff data below the entry for 13:15:00 in row x05.

In the data that you have posted, where does the entry for 06:00:00 in row x63 get counted ?

 

[beldin2]

Sorry I wasn't aware that data are moved relative to each other. I hope this picture will be more helpful.