×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculating number of people working per hour

Calculating number of people working per hour

Calculating number of people working per hour

(OP)
I am attempting to make a formula to calculate the total number of people scheduled per hour based on their start and end times. It seems to work fine until there is a shift that crosses over midnight. I have attached a quick sample of what i am seeing with my formulas and wondering if someone can help me get the correct calculations.

Thank you in advance for any help anyone can offer.

Paul

RE: Calculating number of people working per hour

hi,

1. Your TIME value in G6 is 1/1/1900 7:00:00 AM and correctly so! Every TIME value in this list ought to have a DATE component in order to accommodate the midnight shift to the next day.

2. So the value in G7 and following needs to be

G7: =G6+TIME(1,0,0)

...and COPY N PASTE down.

3. The COUNTIFS formula then is (using named ranges)

H6: =COUNTIFS(shift_Start,"<="&G6-INT(G6),Shift_End,">="&G6-INT(G6))


BTW, I greatly prefer using SUMPRODUCT()

H6: =SUMPRODUCT((shift_Start<=G6-INT(G6))*(Shift_End>=G6-INT(G6)))





Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Calculating number of people working per hour

I saw yet another issue where the Shift End crosses the day.

So I added another column, Shift End1 with the following formula...

E6: =IF(C6>D6,1,0)+D6


This will only work under this restriction: That ALL Shift Start times are for the same day, from midnight (12:00 AM to 11:59 PM)

Then...

H6: =SUMPRODUCT((shift_Start<=G6-INT(G6))*(Shift_End1>=G6-INT(G6)))

And the other thing I did was use Excel's Series feature by entering 7: and 8: in G6 & G7 respectively and then SELECTING G6 & G7 and DRAGGING the handle down thru G29. This will auto increment each cell and add a date value at midnight.

File attached.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Calculating number of people working per hour

(OP)
Thanks Skip, I just got back around to trying to figure this out again.....if you look at the file you sent back to me I think there is still an issue. Lets focus just on the 1:00 am time slot.....It shows 2 people working when it should be 9 people working at 1 am. Am I looking at something incorrectly?

Really appreciate your help here!

Thank you!

Paul

RE: Calculating number of people working per hour

I had two different things going in my head. Since Shift_End1 accounts for the times in the next day, there’s no need to remove thenext day date via INT().

This one should work better. I count 7 by hand and via formula.

H6: =SUMPRODUCT((shift_Start<=G6)*(Shift_End1>=G6))

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close