Sumif formula for time ranges

Bezzina

New Member
Joined
Sep 13, 2019
Messages
5
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 a moderator:

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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
 

Bezzina

New Member
Joined
Sep 13, 2019
Messages
5
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
 

Bezzina

New Member
Joined
Sep 13, 2019
Messages
5
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








<tbody>
</tbody>

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










<tbody>
</tbody>

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










<tbody>
</tbody>

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☹
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
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.
 

Bezzina

New Member
Joined
Sep 13, 2019
Messages
5
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
Re: Help with sumif formula for time ranges

Unfortunately not, it's all done in date order.
 

Bezzina

New Member
Joined
Sep 13, 2019
Messages
5
Re: Help with sumif formula for time ranges

And I guess posts cannot be deleted another amended?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,270
Office Version
365
Platform
Windows
Re: Help with sumif formula for time ranges

I have modified your op to point to post#4
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
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($D2<I$1,$F2>I$1),60,0)))
 

Forum statistics

Threads
1,082,115
Messages
5,363,247
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top