CountIfs with critera that includes if Column A cells > Column B cells

Question

I have an excel worksheet (macro free) set up as follows;

Column W = Shift Start Time 
Column X = Shift End Time

Column AE:BB are named 00:00 to 23:00 respectively.

I am trying to count the manpower available by hour e.g. if 1 shift is Start Time 10:00 and Finish 15:00 - this will count 1 for hours 11:00,12:00,13:00 (within AE:BB Table).

The formula currently used is

=COUNTIFS($B$5:$B$76,"<="&AE4,$D$5:$D$76,">="&AE4)

This works when the Shift start time is earlier than finish in one 24 hour period (e.g. 07:00 - 18:00). However, when a night shift finishes the next working day (E.g. 19:00-07:00) any manpower available within that time will not calculate.

Is there a way to incorporate a criteria that considers if Cell W> adjacent cell X into the formula?

Example Source

Current Results

Expected Results

Time in excel is stored as a decimal representing the percentage/fraction of a day. There is no 24:00 officially though some function (hit and miss) will work with it. However that makes is an unreliable time to use (yes total aside). The date (or days) is the integer. So one way of dealing with time the following day is by adding 1 to the time. By default time without a date will assume 0 as the date. — Forward Ed
one way of dealing with that assuming you cannot have a shift more than 24 hours long, is IF(END TIME<START TIME, END TIME + 1, END TIME). — Forward Ed
@ForwardEd - I have updated with a screenshot to help make this clearer. Yes, as you alluded to there is no 24:00 so the table is limited to 23:00. A shift is very rarely over 12 hours this it should never span two days. In regards to the IF statement - will this only work on a cell by cell basis? — CantExcel
for your screen shots, would it be possible to break it into two. one of the source data and one of the expected results? Right now when clicking on the image its still hard to read. If you are using windows, win+shift+s is a great way to get a quick selective screen capture. — Forward Ed
@forwardEd - I've uploaded further screenshots - I hope this helps :) — CantExcel

Forward Ed Forward Ed · Accepted Answer · 2019-07-02T17:00:06

Based on the shift table as follows:

Note that an additional column was added to the shift hours. It was my method for creating an end time on the following day. In Y5 I placed the following formula and copied down:

=IF(X5<W5,X5+1,X5)

It adds 1 to the time when the end time is less than the start time. This essentially adds 1 day to the time. However as long as the cell is formatted to only display time like the other cells, the 1 day is not observed by the user. This modified end time is then used for checking counts.

Place the following formula in AE5 and copy to the right as needed:

=COUNTIFS($W$5:$W$9,"<="&AE4,$Y$5:$Y$9,">"&AE4)+COUNTIFS($W$5:$W$9,"<="&AE4+1,$Y$5:$Y$9,">"&AE4+1)

Check once for regular times and check again for times in the next day and combine the results. Not the > instead of >=. If a shift ends at 1700 then the shift is not available to work from 1700-1800. In your previous example you had them available for the start time block and the end time block. You should count 1 or the other but not both unless you have something that states shift can work 1 hour past their end time or something similar.

CountIfs with critera that includes if Column A cells > Column B cells

1 Answers