Hi all! I am having difficulty using formulas to calculate the number of employees who are working each hour. I believe this is because my overnight shift is a little tricky. Below is the formula I am currently using and a snip of my scheduleSchedule photo.PNG Any ideas on how to get the overnight to calculate correctly, on the correct day would be greatly appreciated!
=(COUNTIF($C$6:$C$23,">="&A35)-COUNTIF($B$6:$B$23,">"&A35))
Thanks!
Hi and welcome to the forum
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely much use.
Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
hi! Here is the workbook. :0
See if the following will work for you:Formula:
Please Login or Register to view this content.
I have marked the schedules that showed differences between the formula and manual count (6:00 AM - 10:00 AM).
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks JeteMc, this was a huge help!
I am working on this same template and using this same formatting. I am pulling the information from a query with employees from multiple departments. I want the SUMPRODUCT formula to auto-recalculate to show number of employees working at any given hour from the filtered data.
The formula would be placed into B4 for Monday, E4 for Tues, etc.
I am also trying to do the same for the "Starting" and "Leaving" columns for each day of the week and have =COUNTIF(Data[(Mon) START],A4) for "Starting" and =COUNTIF(Data[(Mon) END],A5) for "Leaving." Is it possible to also have these formulas auto-recalculate if the data table is filtered?
Attachment 741080Attachment 741081
@eacreel
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks