Forum Discussion
Counting the number of working staff during specific time periods
m_tarler Thank you for responding.
a) when you want a count for that time range is that anyone that works at all during that period or work for the entire period? It would be for the entire period.
works at any point: ([start time]<=[period end])*([end time]>=[period start])
works whole period: ([start time]<=[period start])*([ent time]>=[period end])
b) how are those start and end times entered? I assume they are not text but are they just time? So wouldn't 23:00 be after 7:00? or is the date included so 23:00 is really 3/9/2024 23:00? Start and end times are entered using just time, no date.
c) you broke out the start and end times nicely on the schedule why no on the periods so you can use a formula to easily grab the start and end of the desired periods? I hadn't thought of that. What would that formula look like?
d) you say it is working but the formula you give doesn't make sense to me that it would work because it is looking at only 1 column. I haven't that faintest idea why it's working but the count is correct?? I am certain it is probably a fluke/coincidence.
e) you list the time ranges starting at 7am and then going through 11p-3a and 3a-7a. Are those last 2 both the times going into the next day but added to the prior day? Correct, our 24 hour period runs from 7am - 7am or is 3a-7a this day's morning shift just in a weird order. And then what about the 11p-3a, would that be either after 11p today AND/OR before 3am today? It would be 11pm today and/or before 3am tomorrow.
f) if you are 'lapping' days then you need some rules about how to treat those values or better yet enter them so they calc correctly. for example 1a the next day could be 25:00 or have the date tied to the time. This is what I needed to know. Is there a clean way to tie the date to the time without entering it into the cell itself. I am open to any advice!
the answer to (c) is either using a formula like I showed in question (a) or with a COUNTIFS(D4:D62,">="&B2) you just put the cell ref into the formula
for (e)/(f) I would try something like this: =A3-TIME(7,0,0)+(A3-TIME(7,0,0)<0) for all time values to shift them by 7 hours so they are all the same day OR you can =A3+(A3<TIME(7,0,0)) for all time values which will add 1 day for all values <7am BUT in your example you have end times at 7:30 SO maybe you use =A3+(A3<TIME(7,0,0)) for START times and =A3+(A3<TIME(9,0,0)) for END times assuming you never have any shift <2 hours.
BTW and maybe most important, what version of Excel are you using? Are you on Excel 365? If so we can make 1 or 2 Lambda functions to make this much easier.
https://wvuhsc-my.sharepoint.com/:x:/r/personal/jennifer_sova1_wvumedicine_org/Documents/Sample%20Schedule.xlsx?d=w10727fcecd50414183fbaec83c06467f&csf=1&web=1&e=nYFdMm
I am, indeed, using excel 365.