LinkBack URL
About LinkBacks
Hello.
In cell B15, I need a formula that will count the employees who worked together in a given period in one workday (see attachment).
Best regards
Hello.
In cell B15, I need a formula that will count the employees who worked together in a given period in one workday (see attachment).
Best regards
Are you still using Excel 2010? Why have you posted in the VBA section if you want a formula? Shall I move the thread for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Try this:
=SUMPRODUCT(((B$4:B$11<=C13)*(C$4:C$11>B13)+((B$4:B$11<=C13)+(C$4:C$11>B13))*(C$4:C$11<B$4:B$11))*(B$4:B$11<>""))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Another way,
Please Login or Register to view this content.
Windknife, yours breaks down if there are times crossing midnight.
Sorry. My mistake, You can switch to the formula section. Thank you.
Thread moved.
What is your Excel version?
Glenn, You are right, my formula doesn't consider times crossing midnight.
Nevertheless, OP's data doesn't happen this situation.
Excel 2021 v2402
Please update your forum profile accordingly. Do this NOW.
Thanks for your help. I will try.
You have done it - thanks.
Thanks for your help. I will try.Originally Posted by windknife
You are welcome.
Dear Glenn Kenned, dear "windknif"
The problem in both previous formulas is in the counting as soon as the start time is entered. Formulas are in E15 and E16.
Last edited by vilipec; 03-29-2024 at 02:24 PM.
Try
Formula:
Please Login or Register to view this content.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Dear JohnTopley,
the formula is not valid in the case as in the attachment
Add the bit in red:
=IF(COUNTA($C$4:$C$11)=0,0,SUMPRODUCT(((B$4:B$11<=C13)*(C$4:C$11>B13)+((B$4:B$11<=C13)+(C$4:C$11>B13))*(C$4:C$11<B$4:B$11))*(B$4:B$11<>"")*($C$4:$C$11<>"")))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks