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).There is attached example solved for two days in K column. Could you please help me?
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).There is attached example solved for two days in K column. Could you please help me?
Hi I don't know what you mean with adequate hour and so but if you add to helper columns you can do that
Place a formula in column C where the hour you want counted is checked and if that is the one set a 1 else a 0 and in column D the same but for the 0.5h
The use COUNTIF() or COUNTIFS() function (look it up) and it might get you the answer
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Adequate hour is the first one during night (or day). 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. Intervals each day will be different. Using Your method I would have to type them manually each time. It would be easer to count them personally. Is there any other solution of the problem?
There must be a rule for when which interval is counted you can all do that in a formula and a helper column and cell.
Maybe you should consider vba
I know there must be some solution and helper column doesn't bother me but I still don't know what formula can be used in it.
The "problem" is how you define "adequate hour":
21:50; 22:20; 22:25; 22:30; 22:50; 23:05; 23:15; 23:4
In the above why is "22:20" selected as the "adequate hour"?
A simple count formula is ..
=COUNTIFS(B:B,">" &$E$1,B:B,"<=" &$E$2)
Where E1 is your "start time" and E2 is your "finish time"
How are E1 and E2 defined?
I suggest you reattach the file and color the cells for the "Adequate hour" green (to illustrate) and the 0.5 you want in the second count color these something else, then the conditions can be clear to see what solution en and definitions can be to be sued in a dynamic formula, it should be possible to do this in such a way that you don not have to edit it with every change.
@John Topley has a point and with the new attached file you will probably give us the information to help
Adequate for starting count 8h intervals - green. Adequate for starting count 0,5h intervals - red. Intervals - yellow. I hope everything is clear now.
Please remove ALL merged cells as they are the source of many problems
Can you explain why you have merged cells?
I will type other data in this sheet. I merged cells because I know that sometimes I will need a place for more than one record. I can unmerge those cells but numbers of records can not change so there will be empty records under those specific cells that were previously merged.
You marked (colored) the cells but what makes you decide that it is and 'Adequate Hour' and I do not really see the 0.5, is that 0.5 in decimals the half an hour or 0.5 is that minutes?
With merged cells and all that it will be complicated, I still think that a macro would be the best option but then again you have to know what the criteria are for the count conditions
0,5h=30min.
The adequate hour for day time (6:00-22:00) is the first one after 6:00. The adequate hour for night (22:00 -6:00) is the first one after 22:00.
Merged cells can be unmerged but there still will be empty spaces and the must stay.
I don't know if macro would be appropriate if so I would gladly use one but consider that I have never used any macro and that is why I prefer formula.
By 'adequate' do you mean 'usual' or perhaps 'normal' (e.g. normal working hours)?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I mean that it should be use as the beginning of first interval during specific day and daytime.
I'll see if I can think something up, I still have a lot of ??? after reading this but let me try and see if I can figure something out first.
Sorry, been busy, it has my attention.
Like I said, I'll try my best and hope to come up with something.
You are devoting your time. I am really greatful for that and I understand that you can be busy with something else. I hope you will find a solution because I really need it and for me or even some other persons knowing excel better than myself the problem is to complicated.
I do not understand you list. why are there full hours missing?
The list is also for different days (dates) what to do?
What are you counting?
It does not make sense (to me), Forget the word adequate, please.
You have two groups 06:00:00 - 22:00:00 day shift and 22:00:00 - 06:00:00 night shift?
What about the half hours.
I am sure the question makes sense to you but we have to understand it to be able to help, I am not going to try an guess what you want.
So please try and give a clear example with what you have and what you want
Of course it is. The formula should count whole sheet not only one day. If there would have been only one day would not be needed.The list is also for different days (dates) what to do?
The number of records in B column in intervals.What are you counting?
Yes. In the first group interval has 8h and in the second 0,5h(30min). The problem is that first interval could start at different hour each shift. First interval during night shift could be 22:00-22:30 but only if there is record with 22.30 in B column. If there isn't it could start in first hour that occurs in in B column. It could be 22:05, 22:15, 5:00 or any other hour that is the highest in B column during this shift. All other intervals should be measured from it so when the first interval during night shift is 22:20-22:50 the second will be 22:50-23:20.It does not make sense (to me), Forget the word adequate, please.
You have two groups 06:00:00 - 22:00:00 day shift and 22:00:00 - 06:00:00 night shift?
Half a hour is the time of interval in shift from 22:00-6:00 while 8h is the time of interval in shift from 6:00-22:00.What about the half hours
I have what s in A an B column and I want to to have number of records from B column during each interval.I am sure the question makes sense to you but we have to understand it to be able to help, I am not going to try an guess what you want.
So please try and give a clear example with what you have and what you want
This is a question that I don't understand.I do not understand you list. why are there full hours missing?
Your answer explains some things an the question about the full hours is that I miss one or two ??:00:00
Let me see.
You want to count all the hours between 06 - 22 that have a record, so on the hour change add one. so all greater and equal to 6 and smaller than 22 for the 'day-shift'
You also want this to be done for the hour between 22 - 06 so all hours greater or equal 22 and smaller than 6 for 'night-shift'
Now the half hours, is that the half hour after the counted hour? so if the first record is 7:35 then I cannot count 30 minutes because 7:35 + 0:30 is 8:05 is this correct?
During the 'night-shift' there will be two dates, count as one or stop counting at 24 (23:59:25.9999) and restart for the next day at 00 ?
With all these 'exceptions' I do think you will have to use a macro.
or many helper columns
Which column and row are you writing abut?full hours is that I miss one or two ??:00:00
That's correct but you must remember that day-shift is not one interval and night-shift also isn't. Day shift is the time that intervals last 8h and night shift is the time when the last 30min.You want to count all the hours between 06 - 22 that have a record, so on the hour change add one. so all greater and equal to 6 and smaller than 22 for the 'day-shift'
You also want this to be done for the hour between 22 - 06 so all hours greater or equal 22 and smaller than 6 for 'night-shift'
YesNow the half hours, is that the half hour after the counted hour?
It would be if 7;35 would be during night shift when the interval has 30min but it is during day shift when intervals has 8h.so if the first record is 7:35 then I cannot count 30 minutes because 7:35 + 0:30 is 8:05 is this correct?
Count as oneDuring the 'night-shift' there will be two dates, count as one or stop counting at 24 (23:59:25.9999) and restart for the next day at 00 ?
I only would like to have it counted. Method is less important.With all these 'exceptions' I do think you will have to use a macro.
or many helper columns
There are too much unclear things. I am sure YOU understand what you want, but it doesn't come across.
I'm not going to try anymore, sorry.
Takes too much of my time which I do not have. It has been several days trying to understand and still I do not understand.
I hope somebody else picks it up and hope he/she can help.
Sorry for that
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks