Excel: track number of employees at a given moment

Question

I am trying to come up with an Excel sheet where I have a set of 30-minute intervals throughout the day (starting at 10:00 and ending at 20:00) and I would like to calculate how many employees are available during each of them.

The data I have is their shift start/end and the start-end times of their lunch breaks (normally 1 hour, but for part-timers these last 30 minutes).

Here's a sample file: http://www.mediafire.com/download/op3rarsiqj1sm77/time-tracker.xlsx

Do you have any suggestions?

Thank you!

Answer 1

You should be able to do this with the logical AND, NOT, and IF formulas, testing for each employee and each time "IF" this time is larger (or equal) than the shift start "AND" "IF" it is smaller (or equal) than the shift stop, "AND" "NOT" within the lunch break (using a similar logic). Assuming that an employee that is arriving or leaving is still present at that time, the formula you would enter in cell G2 of the sample sheet could read

=AND(
     AND(IF(G$1>=$B2,TRUE(),FALSE()),
         IF(G$1<=$C2,TRUE(),FALSE())),
     NOT(AND(IF(G$1>$D2,TRUE(),FALSE()),
             IF(G$1<$E2,TRUE(),FALSE()))))

Copy&paste this to the other cells from G2:AA4 (for each employee and time), and you see when each employee is present ("TRUE") or not ("FALSE"). Then, you can simply add the values for each column, e.g., for column G, you could write in cell G5:

=SUM(G$2:G4)

(Again, copy&paste to the other columns for getting the results for the other times.)

Edit: As Dirk pointed out below, there are more concise ways of writing this as a formula. The advantage of using the lengthier version here would be to allow more customization. However, for this purpose, we'd need to put the formula above "inside out". So if, e.g., you'd want to register the employee absences with texts rather than TRUE/FALSE, e.g., "present" if present and "" if absent, you'd have to use IF as the outside function and AND and NOT inside the IF conditions, like this:

=IF(
     AND(AND(G$1>=$B2,G$1<=$C2),
         NOT(AND(G$1>$D2,G$1<$E2))),
    "present","")

The you can do the final counting by using something like

=COUNTIF(G$2:G4,"present")

Answer 2

You can achieve this using a COUNTIFS-formula.

Just put this in cell G2:

=COUNTIFS($B$2:$B$4,"<="&G1,$C$2:$C$4,">"&G1)-COUNTIFS($D$2:$D$4,"<="&G1,$E$2:$E$4,">"&G1)

and fill over to AA2.