Sumif formula for time ranges

Bezzina

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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,042
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
8
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
8
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
63,990
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
8
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
63,990
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
8
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
63,990
Office Version
  1. 365
Platform
  1. 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,042
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,144,369
Messages
5,723,952
Members
422,528
Latest member
IMK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top