Employee Count per Interval

[sarahj84]

Hi there,

I am looking to do a calculation of how many employees we have during a given interval. I've read a bunch of other threads where it was start time in one column and end time in another but I don't want that. I need start and end time in the same column. I don't know if this can be done with a COUNTIF formula or if I have to use an IF statement to check the start and end time which then produces a "1" and then a formula to add them up.

I haven't made the spreadsheet yet but what I'm basically looking for is this:

Column A will be their name.
Column B-H will be each day of the week.
Each cell will be filled in with what that person works that day.

Then on a separate sheet (or even down below)
Column A will be the interval time 00:00-00:30 all the way to 23:30-00:00
Column B-H will again be each day of the week.
And then I want a total of how many people are scheduled in each interval in each day based on the schedules entered above.

I know this is do-able, I just need the help to get started.

Thanks so much!

[José Augusto]

Hi
Please can you clarify how records the work of each person in a cell each day?

[CAntosh]

Trying to cram two distinct bits of info (start time and end time) into one cell only to have to refer to them separately elsewhere complicates your spreadsheet and limits your flexibility down the road. Why not keep the values separate and make something that's easier to understand and maintain? Take a look at the attachment, which uses a few extra columns but keeps things much simpler. Would something like the attached work?

[sarahj84]

Yes that works. I was just actually in the process of creating that. The only reason that I wanted to keep it in one cell is because we present our shifts to employees like this. So in terms of keeping it simple and having everything formatted the same way and being able to copy/paste from one file to another would be easier.

[sarahj84]

Can you have a look at this? It looks to be working except for the 11:30 PM - 12:00 AM interval. It's counting everyone into there and it shouldn't be. I'm sure it has to with it being a time calculation but I'm not sure the problem.

The time either needs to be just 1 column stating the start time. Or it needs to be 00:00-00:30, 00:30-01:00 etc. It doesn't make sense to have it on the :29 or :01 of the hour. Is there another fix you know of?

[CAntosh]

Excel doesn't know the difference between 12 AM the start of the day and 12 AM the end of the day. The easiest fix is to drop the second clause of the countifs, so that it just counts any shift active after 11:30 PM.

I was actually about to send you my latest version - take a look at the new attachment. You should be able to paste your timesheet into sheet one, and the rest (sheets 2 and 3) will autocalculate. Your raw data timesheet is a bit of mess - sometime there's a space before AM/PM, sometimes not - but I think I accounted for everything. Test it out, hopefully you can just paste your raw data in A1 of the first sheet and be done.

[CAntosh]

Forgot the attachment... it's there now.

[sarahj84]

It's counting 2 people at the 11:30pm-11:59pm interval every day of the week but that isn't the case. Some days only have 1 or like on Sunday we only have someone until 23:30 so it shouldn't count that shift at all.
The data in our work week also runs Mon-Sun instead of Sun-Sat in case that makes a difference on the calculation

Would this formula work if I made the interval times just be a single time instead of 2 cells for a range?
=(COUNTIF($C$7:$C$12,">="&F7)-COUNTIF($B$7:$B$12,">"&F7))

I pasted it into the sheet if you can have a look and tell me why it won't work?

Thanks for all your help!!

[sarahj84]

Sorry - had the formula backwards. Here's an updated attachment. But it's still giving me a negative number now.

[sarahj84]

Sorry - one more update. This has the full week in it now.

[CAntosh]

It looks like the shifts ending at midnight are wreaking havoc on your calculations, otherwise your formula works in theory. I tweaked my version to fix the errors with the midnight crowd and to allow for more than the initial group. See if it works. The placement of Sunday's data has no bearing on the calculations. If you prefer at the end, though, it can be moved. Hopefully with this version you can just paste your raw data on sheet one.

[sarahj84]

That looks better for the last interval but now I'm seeing that on Tues-Thurs for example we have 2 people until midnight and 1 person until 23:30 so the 22:00, 22:30 and 23:00 intervals should reflect a 3 not a 1 shouldn't it?

[CAntosh]

Nuts, you're right! Those midnight shifts are doing a number on my calculations, too. Hold on, fixing...

[CAntosh]

Try this one:

[sarahj84]

Maybe I should mention that we have overnight shifts too....
If I enter 23:30-07:00 is that going to work or should it be on 2 days? 23:30-00:00 on Tues and then 00:00-07:00 on the Wed?

[sarahj84]

Maybe I should mention that we have overnight shifts too....
If I enter 23:30-07:00 is that going to work or should it be on 2 days? 23:30-00:00 on Tues and then 00:00-07:00 on the Wed?

[CAntosh]

Entering it over 2 days gives you much cleaner data, because you can still attach the hours to a particular day/date. If they need to be one entry, then we can scrap our current approach. The formula has to be much different if the end time can often be "before" the start time. Does the raw data come to you listing them as hours only, like in your sample, or is it date + hours?

[sarahj84]

It would be like this. Basically we do a shift bid with our employees so I want this to A) see where our coverage is when planning before hand and then B) see where our coverage is each week as shifts change.

[CAntosh]

Alright, new version, again constructed with the idea of pasting your raw data into sheet1. See if there any holes:

[sarahj84]

That looks like it's calculating properly. Did you just change it to add additional counts in if it was reflected in other days as well? I just like to understand the calculations behind it in case I ever need to change it myself.

If I change the times to show 07:00-07:30 for example instead of :01 and :31 will it mess anything up?

[sarahj84]

I'm trying something else in the same sheet so maybe you can answer this....
I'm trying to take the values in 2 separate cells which are both a time format "6:30:00 AM" and merge them together using CONCATENATE with a "-" in the middle. Instead of actually displaying 6:30:00 AM - 2:30:00 PM it is giving me a 0.270833333333333-0.604166666666667

Why won't that work?

Again, thanks for all your help! You probably think I'm asking crazy questions, lol.

[CAntosh]

Don't fret the questions. As you can tell, dealing with time intervals is a challenge, and I'm grateful for the practice on a slow workday. To address your first question, changing the times on sheet3 from 7:01-7:30 to 7:00-7:30 will require adjusting the formulas to fit. It's not the end of the world, but it would require some tweaking. As to using CONCATENATE on times, there's a bit of a trick to it, since you're swinging from a special "time" format back to standard text when you concatenate. If your times are in B2 and C2, try the following setup:

=CONCATENATE(TEXT(B2,"h:mm AM/PM"),"-",TEXT(C2,"h:mm AM/PM"))

[sarahj84]

Awesome! Thanks!
So now I have the option to either enter it already broken down (meaning start time in one column and end time in another) and have the sheet calculate and give me the shift in the end. Or opposite where I can enter it from the bid and it will break it down and give me the OU. This is great!

The CONCATENATE formula worked perfect, thank you for that! My only question now is if there is no shift in there, how to I get it to enter OFF for example instead of showing 12:00AM - 12:00AM.

I've attached the sheet for you to see. It's on the "Shift Bid" tab. (That's in the OU file)

And lastly, I took the one that you gave me and I adjusted the formulas to go down to row 80 to include the option of adding more shifts. I've attached that one as well (That's the OU Template file). Can you tell me how on the "OU Template" sheet I would adjust it so the times are showing how I want and then the formula is still calculating properly?

[CAntosh]

For the "Off" shifts, use this structure (this formula would go in I2, then fill down):

=IF(Shifts!N3=Shifts!O3,"Off",CONCATENATE(TEXT(Shifts!N3,"h:mm AM/PM"),"-",TEXT(Shifts!O3,"h:mm AM/PM")))

I'll look into the other question...

[CAntosh]

See if this one looks better. I updated the hours so they look better (e.g. 7:00=7:30). The one remaining exception is the block ending at midnight. That one would throw the calculations off, since Excel doesn't know it's a new day. Does it work for you as it is?

[sarahj84]

That looks fantastic! Thank you so much for all your help! I really appreciate it. I'll remember you next time I run into Excel trouble

[CAntosh]

Excellent! Glad I could help, good luck!

[sarahj84]

Hi, me again!

I'm hoping you can help me again with this. I've been using the sheet for a few weeks now but I'm noticing now that it isn't calculating properly on the OU tab for the 11:30PM interval. For example, on Saturday we have 1 shift that works until 12:00AM and 3 shifts that start at 11:30PM but the OU tab is only showing 1 FTE at that interval. I'm not sure if I accidentally changed something or not.

I did change the formula to go down to line 100 because it needed to go farther. As well as I changed the Monday formulas to include the Sunday overnight as it hadn't been doing that before.

If you can have a look at this and tell me what I need to change that would be great!

Thanks so much for your help!

[CAntosh]

I don't recall all of the details of this project, but I do remember that the late night shifts were causing us problems. I built in a new term that should add in the shifts that start in the PM and end in the AM (of the next day). Those appear to be the uncounted workers. The new Monday formula looks like this, with the new part being the SUMPRODUCT bit at the end:

=COUNTIFS('Raw Data'!B$2:B$100,"<="&$A2,'Raw Data'!C$2:C$100,">="&$B2)+COUNTIFS('Raw Data'!$O$2:$O$100,">"&$A2,'Raw Data'!$O$2:$O$100,"<="&$B$15)+SUMPRODUCT(--('Raw Data'!B$2:B$100>'Raw Data'!C$2:C$100),--('Raw Data'!B$2:B$100<=$A2))

I've stuck on an attachment to show how it should look in your workbook. The numbers appeared to fit at a glance, but take a look and see if it works!

[sarahj84]

Yup that looks great! Thank you so much again for your help!!

[CAntosh]

My pleasure, good luck!

[sarahj84]

I need some help again. I have 2 files now - the first one (called OU Template) takes the "shift bid" and breaks it down into 2 cells and then calculates the OU. That one is working properly.
The second one (called OU) takes the shifts in 2 cells already, calculates the OU, and then spits out the shift bid with the times put together.

Firstly, the OU file isn't calculating like the OU template is. It's not doing the formulas properly including overnights. And it has to go Sunday into Monday as well as this more of a "rolling week" template not for specific dates.

Secondly, we have some situation where an event might occur from 3:45-5:45 and it isn't calculating that as .5 on the OU, it's marking it as a 1 which is not correct since it's only half of the time.

Can someone tell me how to fix these?

Thanks!

[CAntosh]

I think I've got it? Take a look at the attachment and see if it works for you. It relies upon the formula below in C2, filled down, then adjusted for the new columns:

=SUMPRODUCT(--(Shifts!$D$3:$D$500<=$A2+1/48),--(Shifts!$E$3:$E$500>$A2),(IF($A2+1/48>Shifts!$E$3:$E$500,Shifts!$E$3:$E$500,$A2+1/48)-IF($A2>Shifts!$D$3:$D$500,$A2,Shifts!$D$3:$D$500))*1440/30)+SUMPRODUCT(--(Shifts!$D$3:$D$500<=$A2+1/48),--(Shifts!$D$3:$D$500>Shifts!$E$3:$E$500),($A2+1/48-IF($A2>Shifts!$D$3:$D$500,$A2,Shifts!$D$3:$D$500))*1440/30)+SUMPRODUCT(--(Shifts!$C$3:$C$500>=$A2),--(Shifts!$B$3:$B$500>Shifts!$C$3:$C$500),(IF($A2+1/48>Shifts!$C$3:$C$500,Shifts!$C$3:$C$500,$A2+1/48)-$A2)*1440/30)

I experimented with some sample values and it looked like it was holding up, but play around a bit and let me know.

EDIT: It should be noted that the formula is an array formula, so it should be confirmed with Ctrl + Shift + Enter instead of the regular Enter

[sarahj84]

Perfecto! Thanks so much for all your help again!!

[CAntosh]

You're welcome, it was a good project!