Addition of time between hours to Countif

Drexl88

Board Regular
Joined
Jan 20, 2020
Messages
75
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top