Count employees working hourly

[sprit36]

Good morning, I need in the attached sheet to count the employees that there are in hour and half hour, in the Result sheet must indicate in each time slot. Thank you.

[leelnich]

Hi sprit36, welcome to Excel Forum. Your profile shows Office 2010, but your upload had an old .xls file extension, so I avoided any post-2003 functions.

The following ARRAY FORMULA goes in Result!B3:

Please Login or Register  to view this content.

Select B3, paste it in the Formula Bar, then press CTRL+SHIFT+ENTER to confirm. Now copy down with the drag handle.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[sintek]

Does OP not want the names of all people working in current time slot to be placed in Result Sheet

[sprit36]

Good morning and thanks for the reply. Is fine, but I do not know why when I extend the example range B$1:B$70 puts.
Or if I try to put this formula on another sheet does not work. I have to do something other than change the references. Thank you.


I do not need the names of the employees.

[leelnich]

When you paste an ARRAY FORMULA in the Formula bar, you MUST press CTRL+SHIFT+ENTER instead of Enter. Otherwise, it will not calculate correctly. If successful, curly brackets {} will surround the formula, indicating that it works with array inputs.

If you wish to give me your Sheet Names and Actual ranges, I can make sure it's written correctly.

Hey- I just discovered the formula in post #2 had an extra space in front of the "=". This one works. (Press C+S+E)

Please Login or Register  to view this content.

[sprit36]

Good morning, thank you very much for the explanation, I have been seeing and the problem I have is that if there is any text in some cell it returns error. Is there a way to omit cells with text? The rest works perfectly, is what I was looking for. thank you very much.
Sheet1.jpg

[leelnich]

This should do it (in Result!B3):

Please Login or Register  to view this content.

Again, it is an array formula, so must be confirmed with CTRL+SHIFT+ENTER.

[sprit36]

I'm sorry for the inconvenience. Can you put it in this example? I've put it and I get error. Thank you.

[leelnich]

Sorry, I have been having problems delivering copy-able formulas all day. Here's the workbook.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[sprit36]

Thank you very much, the solution is perfect. And many thanks for your interest.

[leelnich]

Happy to help, and thank you for the rep!

[sprit36]

Good morning, I reopen this message to take up the same problem and not duplicate it. At the time was solved, but now I have a problem, if the cell has two slots does not work and I do not know if there is any way to solve it. I attached the example solved by Leelnich with the new doubt. Thank you

[AliGW]

You need to change the layout and have one line for each of the time periods.

[sprit36]

I do not need to change the format, I need it to count everything, cells that have two values in the same cell and those that only have one time slot. Thank you.

[AliGW]

OK - but you are making it unnecessarily difficult for yourself by insisting on that layout.

[sprit36]

If it can be done by changing the design it would also help me. The important thing is that I count it.

[AliGW]

Just change the layout to this and your current formula works fine:

Excel 2016 (Windows) 32 bit

	A	B	C
3	Employee 1
4	Employee 2	10:00-12:00	10:00-12:00
5	Employee 3	08:00-12:00	08:00-12:00
6	Employee 3	13:00-15:00	13:00-15:00
7	Employee 4	08:00-12:00	08:00-12:00
8	Employee 4	13:00-15:00	13:00-15:00
9	Employee 5	10:00-12:00	10:00-12:00
10	Employee 6	10:00-12:00	10:00-12:00
11	Employee 7	10:00-12:00	10:00-12:00
12	Employee 8	10:00-12:00	10:00-12:00
13	Employee 9	10:00-12:00	10:00-12:00
14	Employee 10	10:00-12:00	10:00-12:00
15	Employee 11	10:00-12:00	10:00-12:00
16	Employee 12	10:00-12:00	10:00-12:00
17	Employee 13	10:00-12:00	10:00-12:00
18	Employee 14	10:00-12:00	10:00-12:00
19	Employee 15	10:00-12:00	10:00-12:00
20	Employee 16	10:00-12:00	10:00-12:00
21	Employee 17	10:00-12:00	10:00-12:00
22	Employee 18	10:00-12:00	10:00-12:00
23	Employee 19	10:00-12:00	10:00-12:00
24	Employee 20	10:00-12:00	10:00-12:00
25	Employee 21	10:00-12:00	10:00-12:00
26	Employee 22	10:00-12:00	10:00-12:00

Sheet: Sheet1

[sprit36]

What I need then is a macro that looks for cells that have two time slots and separate in two columns, is that I have many records. Thanks

[AliGW]

I can't help you with that, sorry - I don't 'do' macros - but somebody else should be able to.

I've put out a call for help with this bit.

[sprit36]

Thank you very much, to see if anyone can help me.

[Arkadi]

Perhaps this will help? It does assume that there are no more than 2 time slots per row, if there can be more then it would require some rewriting:

Please Login or Register  to view this content.

I should note that I arrived to help because AliGW put out a call to see if we could help sort this out, so you can thank her if this helped.

[sprit36]

Good morning, thank you for your interest, and thanks to AliGW for the call. It works perfectly, only two questions, if I want to apply from the D column to column AI as I have to change it. He also wanted to know if it is possible that when you copy below the rest of the hour Strip does not put the name, as the attached picture. (this last is not important) Thank you very much for your help.
no name.png

[AliGW]

I can't answer your first query, as that is to do with the VBA, but the answer to the second query is: the name must be there on both rows for the formula to work, which is the most important thing, after all. If the macro isn't adding the name to both rows, then it needs to be adjusted to make this happen (again, I don't have the expertise to do this - sorry).

[sprit36]

It was not to delete all the names, only the name of the employee that does not duplicate, being the time zone underneath it was not necessary that its name comes out. But that's not important, that's fine. My question was to apply columns D to AI. I hope Arkadi can answer my doubt. Thanks for your help.

[AliGW]

it was not necessary that its name comes out

It is necessary that names are duplicated for the formula on the other sheet to work properly.

[sprit36]

It is not by design, it is 31 days. That was why the columns D to AI were in doubt.
days.png

[Arkadi]

The code below will insert new lines if col B contains 2 time entries, and then will also update columns C to AI as well (the j loop does this)

As AliGW pointed out, your formulas for counts do require that the names be present in column A.

Please Login or Register  to view this content.

[sprit36]

Thank you very much for your help, it works properly. Good solution. Thank you.

[Arkadi]

sprit36,

Thanks for the feedback, the rep, and for marking the thread as solved... much appreciated

I'm happy to hear that it works well for you.

[Pepe Le Mokko]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).