Addition of time between hours to Countif

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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

1579606582455.png
 
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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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.

Rather than uploading a file to a share site, you can download the XL2BB add-in that enables you to post sample data to the board. XL2BB
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

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