LinkBack URL
About LinkBacks
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.
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.
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:
Formula:
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
Last edited by leelnich; 05-16-2017 at 10:28 AM.
Does OP not want the names of all people working in current time slot to be placed in Result Sheet
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
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.
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)Formula:Please Login or Register to view this content.
Last edited by leelnich; 05-16-2017 at 09:51 AM.
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
This should do it (in Result!B3):Formula:Please Login or Register to view this content.
Again, it is an array formula, so must be confirmed with CTRL+SHIFT+ENTER.
Last edited by leelnich; 05-17-2017 at 02:11 AM.
I'm sorry for the inconvenience. Can you put it in this example? I've put it and I get error. Thank you.
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
Thank you very much, the solution is perfect. And many thanks for your interest.
Happy to help, and thank you for the rep!
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
You need to change the layout and have one line for each of the time periods.
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
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.
OK - but you are making it unnecessarily difficult for yourself by insisting on that layout.
If it can be done by changing the design it would also help me. The important thing is that I count it.
Just change the layout to this and your current formula works fine:
Excel 2016 (Windows) 32 bit
10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00 10:00-12:00
Sheet: Sheet1
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
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.
Last edited by AliGW; 07-18-2017 at 03:45 AM.
Thank you very much, to see if anyone can help me.
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:
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.
Please Login or Register to view this content.
Last edited by Arkadi; 07-18-2017 at 09:21 AM.
Please help by:
Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know
There are 10 kinds of people in this world... those who understand binary, and those who don't.
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
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).
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.
It is necessary that names are duplicated for the formula on the other sheet to work properly.it was not necessary that its name comes out
It is not by design, it is 31 days. That was why the columns D to AI were in doubt.
days.png
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.
Last edited by Arkadi; 07-19-2017 at 08:38 AM.
Thank you very much for your help, it works properly. Good solution. Thank you.
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.
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).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks