Results 1 to 10 of 10

Thread: Sumif formula for time ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Sumif formula for time ranges

    Hi All
    Sorry for being a noob but I'm trying to fid a formula that will allocate the time spent on a job

    EDIT:
    More details in post#4
    Last edited by Fluff; Sep 13th, 2019 at 01:14 PM.

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,027
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with sumif formula for time ranges

    welcome to the board

    You need to add more information to questions, such as what format you're entering data in, whether you're entering just start and end times or whether there are multiple records etc.

    It will help you to understand how Excel calculates dates and times. A date is a whole number between 1 (01 Jan 1900) and 2,958,465 (31 Dec 9999), but shown in a way that looks like a date. Similarly, a time is the decimal element of a number, so 8:00am = 8/24 = 1/3 = 0.3333, and 12 noon = 0.5. Change your number formats (ctrl + 1) to see this in action

    So if you enter a pair of time values, you can simply subtract the start from the end, giving you a duration in time format. Similarly you can add a series of pairs and subtract the sum of starts from the sum of ends. Working with times like this is far preferable to trying to add whole numbers and then calculate the differences yourself - Excel has already taken account of the nuances that you need to consider. Bear in mind that if working past midnight you'll need to add an additional day to the end time in order to calculate correctly: for example 8pm to 8am = 12 hours = 0.5 as the underlying number = 1.333333 - 0.8333333 = (1 day 8 hours) - (0 days 20 hours)

    HTH
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with sumif formula for time ranges

    Hi Thanks for trying to help with no real info

    I tried to edit it as posted accidentally...........is there a way i can upload my worksheet and obviously also supply a lot mor information

    Thanks again

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Help with a Sumif statement for allocating time spent on job into hourly componants

    Hi All

    Sorry for being a noob but I'm trying to find a formula that will allocate the time spent on a job into each hourly slot in a day

    For instance – below excel mock-up as I can’t upload my worksheet

    A B C D E F G
    1 Day Started Time Started Finish time Duration in hours
    2 Friday 00:26 06:07 5.68
    3 Sunday 04:26 07:07 2.68
    4

    I need a formula that can allocate the time spent working into each hourly slot so I end up with the following output…

    A B C D E F G H I
    1 Hours
    2 00 01 02 03 04 05 06 07 08
    3 34 60 60 60 60 60 07 0 0
    4
    5

    Also happy if it shows as a fraction of an hour………..

    A B C D E F G H I
    1 Hours
    2 00 01 02 03 04 05 06 07 08
    3 0.57 1 1 1 1 1 0.17 0 0
    4
    5

    I assume its some sort of =Sumif statement with =< & => functions?

    Appreciate any help and thanks in advance as I have 10,000 rows of data☹

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help with a Sumif statement for allocating time spent on job into hourly componants

    @Bezzina
    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. (rule 12 here: Forum Rules).

    I have merged both threads.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with sumif formula for time ranges

    Hi thanks for amalgamating the thread however I did try to delete the original post.

    Is there any way my last message can be At the top of the thread as my original post does not offer much information for anyone willing to help

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help with sumif formula for time ranges

    Unfortunately not, it's all done in date order.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with sumif formula for time ranges

    And I guess posts cannot be deleted another amended?

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,133
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Help with sumif formula for time ranges

    I have modified your op to point to post#4
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,027
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with sumif formula for time ranges

    Hi Bezzina, it looks like you want your analysis on a line by line level, which can be done using HOUR and MINUTE along with a nested IF. I've broken it into several parts to help reading, and because there's a lot of repetition so I'm looking to reduce the total number of calculations

    I've placed your sample times in cells A2:B3

    I then get the key outputs for simplicity in reading and creating the main formulas
    D2 =HOUR(A2)
    E2 =MINUTE(A2)
    F2 =HOUR(B2)
    G2 =MINUTE(B2)

    Headers next
    I2:AG2 = 0, 1, 2 etc

    Main formula in I2, copies across and down:
    =IF($D2=I$1,60-$E2,IF($F2=I$1,$G2,IF(AND($D2I$1),60,0)))
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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