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?
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.
SergeiBaklan 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?
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.
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.
mathetes , SergeiBaklan 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.
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!
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.