# Addition of time between hours to Countif

#### Drexl88

##### Board Regular
Hi,

I have the below formula in cell R8 which calculates the total hours worked based on abbreviations of the type of shift and the standard hours the specified shifts cover. The problem I now have is that where shifts are split between employees to cover sickness there is a need to indicate the actual times each employee is working that day (see k8). Is it possible to add onto the formula below to count additional hours worked (D8:Q8) if the hours in those cells that require it were in time format i.e. 09:00 - 13:00.

Any help would be much appreciated.

=SUM(COUNTIF(D8:Q8, {"D","N","DC","B/D"}))*12.25+COUNTIF(D8:Q8,"TWL")*12

Last edited by a moderator:

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

##### MrExcel MVP, Moderator
If you enter the actual hours worked (ie 4:00) then you could just use
=SUM(COUNTIF(D8:Q8, {"D","N","DC","B/D"}))*12.25+COUNTIF(D8:Q8,"TWL")*12 +sum(D8:Q8)

#### Drexl88

##### Board Regular
Thanks Fluff

The issue is where the shifts are split the employees need to be able to see what time they are working not how many hours. However, my calculations need to be the total number of hours. Is it possible?

#### Fluff

##### MrExcel MVP, Moderator
As long as they won't be working past midnight, replace the sum(d8:q8) with
SUMPRODUCT(--IFERROR(RIGHT(D8:Q8,5)-LEFT(D8:Q8,5),0))*24

#### Drexl88

##### Board Regular

As long as they won't be working past midnight, replace the sum(d8:q8) with
SUMPRODUCT(--IFERROR(RIGHT(D8:Q8,5)-LEFT(D8:Q8,5),0))*24
Hi Fluff,

When you say not working past midnight, do you mean starting before i.e 23:00 and working past to say 02:00:00 but would be ok if working from 02:00:00 to 06:00:00.

I have added it to the end of the formula and changed K8 to 09:00:00 - 13:00:00 but it has taken hours away leaving the total at 64.50 hours worked. I could upload an example of the file if that would help ?

#### Fluff

##### MrExcel MVP, Moderator
As long as the hours are on the same day, it's fine, but if they start today & finish tomorrow then it won't work.

#### Fluff

##### MrExcel MVP, Moderator

Have a look at this
Book1
ABCDEFGHIJKLMNOPQRST
1
2
3
4
5
6
7
84DCDOffOffDCOffOff09:00 - 13:00DCDCDOffOffOff77.5
Temp
Cell Formulas
RangeFormula
B8B8=SUMPRODUCT(--IFERROR(RIGHT(D8:Q8,5)-LEFT(D8:Q8,5),0))*24
R8R8=SUM(COUNTIF(D8:Q8, {"D","N","DC","B/D"}))*12.25+COUNTIF(D8:Q8,"TWL")*12+SUMPRODUCT(--IFERROR(RIGHT(D8:Q8,5)-LEFT(D8:Q8,5),0))*24

#### Drexl88

##### Board Regular
All working. thank you so much.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### Drexl88

##### Board Regular
Hi Fluff,

I have now come across a problem that you pointed out may occur, staff now cover shifts that start before and pass midnight...is there an adaption to the above formula that would work?

Thanks

Replies
0
Views
216
Replies
8
Views
117
Replies
0
Views
64
Replies
4
Views
231
Replies
0
Views
439

1,130,029
Messages
5,639,624
Members
417,101
Latest member
amoverton2

### 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.

### Which adblocker are you using?

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

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