LinkBack URL
About LinkBacks
Hi,
Required a formula help to return a sum of hours like (Normal Hrs,OT1 Hrs,OT2 Hrs) in column R6:T10,as based on data available in column C:P
Sample sheet with expected results are enclosed.
Thanks,
Hi,
Required a formula help to return a sum of hours like (Normal Hrs,OT1 Hrs,OT2 Hrs) in column R6:T10,as based on data available in column C:P
Sample sheet with expected results are enclosed.
Thanks,
Please try
=LET(h,IF($C$5:$P$5="Hrs",C6:P10,),d,WEEKDAY($C$4:$P$4),c,SEQUENCE(COLUMNS(h),,,0),
no,MMULT(IF(h>10,10,h),c),ot,MMULT(IF(d>1,IF(h>14,4,IF(h>10,h-10,)),),c),CHOOSE({1,2,3},no,ot,MMULT(h,c)-no-ot))
I used some SUM and IF formulae to match your results.
In R6: =SUM(IF($C$5:$P$5="Hrs",IF($C6:$P6<=10,$C6:$P6,10)))
In S6: =SUM(IF($C$5:$P$5="Hrs",IF($C6:$P6>10,$C6:$P6-10,0)))-SUM(IF($C$5:$P$5="Hrs",IF($C6:$P6>14,$C6:$P6-14,0)))-SUM(IF($C$5:$P$5="Hrs",IF($C$3:$P$3="Sun",IF($C6:$P6>10,$C6:$P6-10,0))))
In T6: =SUM(IF($C$5:$P$5="Hrs",IF($C6:$P6>14,$C6:$P6-14,0)))+SUM(IF($C$5:$P$5="Hrs",IF($C$3:$P$3="Sun",IF($C6:$P6>10,$C6:$P6-10,0))))
(all copied down to other rows).
Last edited by nick.williams; 03-03-2022 at 09:18 AM. Reason: Added formulae
@Nick
We ask that you include formulae within your post as well so that anyone unable to access the attachment will still be able to benefit. Please add these to your post. Thank you.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Done, sorry.
Many thanks all of you for your nice solutions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks