How to add a count to the time slot that falls under a specific time range

0

I want to know how many employees were available at a given point of time.

My data: I have an excel which shows my staffing per shift. We have overlapping shifts.

I have mentioned different time slots, I want the count in each time slot should auto populate whenever i fill or change numbers in the shifter. Please see attached.

I cant change the shifter format as it is an automated report.

Thanks in advance!

Ananth

Answer
Discuss

Answers

0

I'm not certain that you will be able to get an accurate result given how the data is exported into Excel.

However, you could try this formula:

=SUMIFS($D$3:$D$8,$A$3:$A$8,"<="&$A13,$B$3:$B$8,">="&$B13)

Before that though, you must split column A into A and B, where the start time is in Column A and the Finish time in Column B and you must also ensure that both columns of values are treated as dates.

Here is our tutorial on converting incorrectly formatted values to dates but I just edited the cells, removed all non-date data and then hit enter so it would read the values as dates.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login