Forum Discussion
Can Excel do this?
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 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.
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.