Forum Discussion
Can Excel do this?
Hey, I am working on a school project and I would like to know if what I want to create is even possible in Excel.
I have a simple table with employees, hours and salary. What I want to know if is it possible to just provide Excel with a date and hour and it would automatically count how many hours an employee had worked, select shift by itself and count the salary according to selected shift and money rate? I know just some basics from my statistics course at college and I am not sure if Excel (or myself perhaps) could do it.
I have uploaded my sheet so anyone can see what I mean. I am thankful for every reply.
You've said it's a school project and you want to know if what you want to create is even possible in Excel. Right?
The answer to that question is "Yes, it is possible."
It probably would take a bit more explanation of what you want to do, but Excel is very good at that kind of thing. So since it's a project for school, have at it. I'll point you in the direction of the many functions that enable one to look things up from a table. And I'd suggest searching (probably on Google) for how those work.... there are in Excel several ways to accomplish it.
Or were you expecting somebody here to do your homework for you?
15 Replies
You've said it's a school project and you want to know if what you want to create is even possible in Excel. Right?
The answer to that question is "Yes, it is possible."
It probably would take a bit more explanation of what you want to do, but Excel is very good at that kind of thing. So since it's a project for school, have at it. I'll point you in the direction of the many functions that enable one to look things up from a table. And I'd suggest searching (probably on Google) for how those work.... there are in Excel several ways to accomplish it.
Or were you expecting somebody here to do your homework for you?
mathetes Sir, I have managed to create almost my whole table. I only have a problem with Shift H, which starts at 17:00 on Friday and ends at 6:00 on Monday. Could you please provide me with some help with column L? I know I need to add =IF(C2="Friday" then do this, if not, do that, but I can't get it to work.
What you've done is impressive. Instead of giving you the answer, I want to give you a few other pointers.
- First of all, I think you're not entirely correct with your assumption that you need to begin with =IF(C2="Friday" because (if I understand correctly how this is working), the "H" Holiday rate would apply for somebody starting on Saturday or Sunday as well as starting on Friday. So there are more conditions that need to be taken into account in determining whether the hours are paid as "H" hours. So you need to spell those out as well.
- Second, I've pointed you to several functions that might come in handy. You created a table that looks up the name of the day based on dates in September. There's a function that does that automatically, with a lot of variations within it. I've given you a simple example of that. The =WEEKDAY() function returns a number from 1 to 7. You could use those numbers in your formulas rather than the names of the days--it would make the formulas shorter, to some extent. But also, you could do math with them. So if Friday was day 5, Saturday day 6, and so on....your IF statement could simply say =IF(day>=5,.........)
- Third, if you want the name of the day to appear, use =CHOOSE() to turn the number into the name of the day.
- Fourth, in my very first response, I had suggested you look for how Excel can be used to retrieve data from a table. You've chosen to use IF statements for that. I've given you a demonstration of how you can use INDEX and MATCH to get the hourly rates and apply them. I changed the headings of the shift column so as to be able to use the D, E, N, H designations as the reference in MATCH....
So let me turn it back to you now, with those pointers, and see if you can resolve your weekend issue.
mathetes No, not at all, sir, I want to do it myself, but I didn't know if Excel was even capable of doing it. I'll try my best to do it with the help of Google. Thank you very much for your reply!
