Can Excel do this?

[mikim1kolaj]

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.

[#2 · Accepted solution]

@mikim1kolaj 

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?

[#3]

@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!

[#4]

@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.

[#5]

@mikim1kolaj 

What you've done is impressive. Instead of giving you the answer, I want to give you a few other pointers.

1. 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.
2. 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,.........)
3. Third, if you want the name of the day to appear, use =CHOOSE() to turn the number into the name of the day.
4. 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.

[#6]

@mathetes Thank you very, very much. I couldn't get clues like that in Google, that's why I asked you, sir. I will try to implement those functions over the weekend and I'll get back to you when I manage to make it work.

[#7]

@mikim1kolaj 

Here's a great website for help with Excel functions. https://exceljet.net/

I recommend also looking at examples on that site for the functions WORKDAYS, VLOOKUP, XLOOKUP, as well as the ones I included in my sample.

[#8]

@mathetes Thank you, sir. I have used the functions you provided, as well as I got help from exceljet. In cell Q13 I have created a function which gives H or 0, depending on the number of day from WEEKDAY and hour. I have then created a new sheet with 31 days of this function. Then, for every shift in K, L, M, N I entered IF statement, which depending on the outcome of the function in new sheet either counts the number of hours in each shift or just gives 0,00 and continues. 

It works to a point in which two shifts collide with each other. When it is Friday >=17:00 up until Monday <=06:00 it works really good, however, when an employee works for example Monday 04:00 until Monday 12:00 it gives 6hrs in K, 0,08hrs in H, and loses the rest of the time. Did I overdid it to a point where it just simply can't work or is it fixable?

I am really grateful for your help, if I am abusing your free time please just let me know.

Also, it is difficult for me to explain what I did and what I mean because English is not my main language, therefore I am sorry if you can't understand me clearly.

[#9]

@mikim1kolaj 

You are explaining yourself very well in English.

You might (this is a guess on my part) benefit from the IFS function...take a look at it on exceljet. It sometimes is easier than nesting IF within IF within IF.

I will not be able to take a close look until much later today, I'm afraid. I had wondered about that matter of crossing shift boundaries, but it did seem as if you had it resolved in your earlier version. So keep working....I'll look later, but maybe you will be able to do it on your own. That's always more satisfying.

[#10]

@mikim1kolaj 

I'd suggest to start from day one. Shifts could be defined as

in final version format Shift Star/End as time to hide years.

Into the main table I added two helper columns (S and T)

not to complicate main formula, but mainly to clean confusing records as start on Sun 22:00 and end on Sun 03:00.

Formulas here

Start:
=MOD(B3-1,7)+1+MOD(E3,1)

End:
=MOD(B3-1,7)+1+MOD(I3,1)+IF(MOD(I3,1)<MOD(E3,1),1,0)

payment is calculated as

=SUMPRODUCT(
  IF( (S3>ShiftStart)*(T3<ShiftEnd),(T3-S3)*Rate*24,
    (S3>ShiftStart)*
    (S3 <ShiftEnd)*
    (IF( ShiftEnd<=T3,ShiftEnd,T3) - S3)*Rate*24+

    (T3>=ShiftStart)*
    (T3<=ShiftEnd)*
    (T3-IF(ShiftStart>=S3,ShiftStart,S3))*Rate*24
  )
)

Here ShiftStart, ShiftEnd and Rate are named columns in helper range with shifts.

As a cosmetic comment to previous formulas, it's not necessary to use formulas as

=CHOOSE(C3,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

to return weekday name, it's enough to apply dddd custom format to date.

[#11]

@Sergei Baklan Sir, thank you for your time and help.

This cleans everything up for sure, however I still cannot make it work when shift H and D collide with each other..

In row 3 for instance if an employee starts work Sun 22:30, and ends work Mon 06:30, the outcome of working hours is correct - 8hrs, but neither my (with great help of @mathetes ) formula, nor yours work correctly. The sum product of these hours should be: 7.5hrs in shift H, 0.5hrs in shift D, multiplied by defined rates should give: 2992$. The same goes for Sun 23:30 - Mon 07:00.

Do I need to add more rows with defined hours to the sheet H for example :

Sun 22:30	Mon 06:30
Sun 23:00	Mon 07:00
Sun 23:30	Mon 07:30

and index them with letters for example H1, H2, H3 and if that shift occurs the SUMPRODUCT would count it?

[#12]

@mikim1kolaj 

Yes, that was missed. I added the condition if both TiimeStart and TimeEnd are outside the hours:

=SUMPRODUCT(
  IF( (S3>ShiftStart)*(T3<ShiftEnd),(T3-S3)*Rate*24,
  IF( (S3<=ShiftStart)*(T3>=ShiftEnd),(ShiftEnd-ShiftStart)*Rate*24,
    (S3>ShiftStart)*
    (S3 <ShiftEnd)*
    (IF( ShiftEnd<=T3,ShiftEnd,T3) - S3)*Rate*24+

    (T3>=ShiftStart)*
    (T3<=ShiftEnd)*
    (T3-IF(ShiftStart>=S3,ShiftStart,S3))*Rate*24
  )
 )
)

Please check in attached file.

[#13]

@mikim1kolaj 

With you, I appreciate that @Sergei Baklan has, from all appearances, been able to resolve all of your functional issues.

I still want to puzzle through some of this to make sure I understand the use of MOD and SUMPRODUCT in this connection. I'm sure anything I would have come up with would have been more rudimentary, but at least I would have been able to explain it.

Thanks for a most interesting problem!

[#14]

@mathetes , @Sergei Baklan I would really like to thank both of you for the help you have provided me with. A week ago I didn't know more than SUM and AVERAGE in Excel, now I am more aware of its powers and it made me sign up for a course so I can fully understand this great piece of software. Perhaps one day I could help someone here who might be in a position like I was these couple days ago. Once again, great thank you for the help, I appreciate it very much and I wish all the best for you. 

[#15]

@mathetes , the idea behind is that dates are integers and start from Jan 01, 1900 which is Sunday. With MOD(date, 7) we move any date into this first week and work with datetime only within it. MOD(time,0) is to be more safe, to be sure we have only time, not time plus one-two dates in case of incorrect entry.

SUMPRODUCT checks if start/end time is within time range for defined datetime, or our of the range, or one of the is in the middle and depends on that takes the difference.

[#16]

@mikim1kolaj , you are welcome, glad to help. Excel is infinite, good luck on the way of improving your skills in it - that's never ended process.