+ Reply to Thread
Results 1 to 23 of 23

counting numbers in different time intervals

  1. #1
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    counting numbers in different time intervals

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  3. #3
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    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?

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  5. #5
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    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.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,222

    Re: counting numbers in different time intervals

    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?

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  8. #8
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    Adequate for starting count 8h intervals - green. Adequate for starting count 0,5h intervals - red. Intervals - yellow. I hope everything is clear now.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,222

    Re: counting numbers in different time intervals

    Please remove ALL merged cells as they are the source of many problems

  10. #10
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    Can you explain why you have merged cells?

  11. #11
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    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.

  12. #12
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  13. #13
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    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.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,342

    Re: counting numbers in different time intervals

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  15. #15
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    I mean that it should be use as the beginning of first interval during specific day and daytime.

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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.

  17. #17
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    Sorry, been busy, it has my attention.
    Like I said, I'll try my best and hope to come up with something.

  18. #18
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    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.

  19. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  20. #20
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    The list is also for different days (dates) what to do?
    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.
    What are you counting?
    The number of records in B column in intervals.
    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?
    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.
    What about the half hours
    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.
    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
    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 do not understand you list. why are there full hours missing?
    This is a question that I don't understand.

  21. #21
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

  22. #22
    Registered User
    Join Date
    12-20-2017
    Location
    Poland
    MS-Off Ver
    2010
    Posts
    15

    Re: counting numbers in different time intervals

    full hours is that I miss one or two ??:00:00
    Which column and row are you writing abut?
    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'
    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.
    Now the half hours, is that the half hour after the counted hour?
    Yes
    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?
    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.
    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 ?
    Count as one
    With all these 'exceptions' I do think you will have to use a macro.

    or many helper columns
    I only would like to have it counted. Method is less important.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021
    Posts
    7,546

    Re: counting numbers in different time intervals

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Converting mixed numbers into time intervals
    By BryceVBA in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-08-2017, 11:45 AM
  2. Counting between time intervals
    By Climaxgp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-25-2013, 05:28 AM
  3. Converting Data in Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 02:42 AM
  4. Need to Transpose Irregular Time Intervals into Regular Time Intervals
    By AlexJT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2011, 08:30 PM
  5. [SOLVED] Counting cells with intervals
    By Dave R. in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 06:05 AM
  6. Counting cells with intervals
    By Paulo Araújo in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Counting in intervals
    By John Smith in forum Excel General
    Replies: 1
    Last Post: 01-30-2005, 08:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1