Sumif formula for time ranges

[Bezzina]

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

 

[baitmaster]

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]

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]

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]

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]

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]

Re: Help with sumif formula for time ranges

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

 

[Bezzina]

Re: Help with sumif formula for time ranges

And I guess posts cannot be deleted another amended?

 

[Fluff]

Re: Help with sumif formula for time ranges

I have modified your op to point to post#4

 

[baitmaster]

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)))