break down hours between two times.

[daveyb60]

I have a spreadsheet with around 50 peoples work hours, two of the columns are time in and time out, the workers start at different times, some start at 02:00 till 10:00 some start 02:00 till 06:00 and some start 04:00 till 10:00 ect ect, i'm having trouble finding a formula to find the hours between two times for example all the hours between 02:00 and 03:00 buy using the two columns of time in and out and was wondering if anyone could help?
thankyou in advance

Dave

 

[smitpau]

Hi,

I believe if the data is correctly formatted in time, then it would just be the (time out cell) - (time in cell).

 

[daveyb60]

below is the table, what i am trying to achieve is the hours from the start/finish times to be added up and shown in the cell next to the relevant column on the right so in the cell next to 02:00 - 03:00 it would show 2 and in the 03:00 -04:00 it would show a 3 as there are that many instances of them hours being worked

 

[smitpau]

Okay I see this should contain the info,

Count times in a specific range

To count times that occur within a certain range, you can use the COUNTIFs function. In the example shown, the formula in E7 is: =COUNTIFS(B5:B11,">="&E5,B5:B11,"

exceljet.net

Try modifying this formula and it should work

=COUNTIFS(B5:B11,">="&E5,B5:B11,"<"&E6)

 

[smitpau]

So you'd need the start and finish times to be in two different cells.

 

[gaz_chops]

Better in 2 cells, but this would work

=COUNTIFS(D2:D8,">="&LEFT(F9,2)/24,D2:D8,"<"&MID(F9,6,2)/24)

 

[daveyb60]

super they are both good and work well, however is there a way that they can do parts of hours as it is only counting whole numbers?

 

[gaz_chops]

I don't follow, better to post another example, show the answer you are getting & the answer you want.

In your OP you say 0200-0300 should be 2, yet none are between 2 & 3 hours!

 

[daveyb60]

Hi Gaz, sorry about not being to clear?, only new to Excel, so as you can see from the table the time on the second row is from 2:45 till 04:00 so the 2 till 3 slot on the right hand table would need to be 1.15.

Thanks again

Dave

 

[gaz_chops]

I'm still a bit confused,
It seems you simply want to sum the hours if the Start time is between the 2 times on the right, like below

If this is correct, try

=SUMIFS($D$2:$D$8,$B$2:$B$8,">="&F12)

Note:
D is the Start Time
F is the 02:00:00

If i am wrong, provide an example with the answers you expect.