I have a timesheet template that I want to add the total hours worked but have the result be at least 7.6. Our employees are paid 7.6 hours per day but only work 7.5, so they accrue their RDO. My formula I have so far is =((D6-B6)*24)-C6
for example:
Date Start Lunch Finish Total
14/05/15 (Thu) 9:00 AM 1 5:30 PM 7.50
15/05/15 (Fri) 9:00 AM 1 5:30 PM 7.50
Try:
Formula:
=max(7.6,((D6-B6)*24)-C6)
Best Regards,
Kaper
That's close. The only problem is if the employee didn't work that day it will put 7.6 hours, which I need it to say 0.00hrs.TEMPLATE - Timesheet.xlsm
An IF function would be good ()) but I can't join the two formulas together
Existing formula
=((D6-B6)*24)-C6 then some how add =IF(E6=7.5,0.1,0).....
Something like this, but one that works...mine doesn't =((D6-B6)*24)-C6,(IF(7.5,0.1,0))
=text(mod(d6-b6,1)*24-c6,"0.00")
try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Do you mean:
=IF(((D6-B6)*24)-C6=7.5,0.1,0)
Formula:=IF(OR(B6="",D6=""),"",MAX(7.6,((D6-B6)*24)-C6))
or if you prefer zeros over empty cells:
Formula:=IF(OR(B6="",D6=""),0,MAX(7.6,((D6-B6)*24)-C6))
Getting closer, though if they only work 6hrs I need it to say 6.
So in a nutshell I need it to add up the hours worked. If up to 7.5 the actual hours worked needs to show, if 7.5 worked it needs to round up to 7.6, if over 7.5 hours worked the total hours worked needs to show.
Formula:=IF(OR(B6="",D6=""),"",if(and((D6-B6)*24-C6>=7.7,(D6-B6)*24-C6<=7.6),7.6,(D6-B6)*24-C6)
May be it would be wise/kind to tell us about all special cases at once ...
Attach a sample file with some examples and expected result to avoid confusion
See the attached, use the Test - Fulltime tabTEMPLATE - Timesheet.xlsm. I have placed Kapers formula in E6 but its not working.
PS.
Siva
Your formula
Formula:=IF(MOD(D6-B6,1)*24-C6=7.5,7.6,MOD(D6-B6,1)*24-C6)
will return more than 7.6 if more than 7.6 was reported, while Skumby asked: "I need the cell to be a maximum on 7.6 (even if they work 10hrs)."
"When they work overtime I want it to show the hours they worked" this is what he was mentioned in Cell I8, in the attached Excel FileOriginally Posted by Kaper
PS.
Siva
Your formula
Formula:=IF(MOD(D6-B6,1)*24-C6=7.5,7.6,MOD(D6-B6,1)*24-C6)
will return more than 7.6 if more than 7.6 was reported, while Skumby asked: "I need the cell to be a maximum on 7.6 (even if they work 10hrs)."
Have you tried to read and understand the formula - there is obvious (I think) typo:
Formula:=IF(OR(B6="",D6=""),"",if(and((D6-B6)*24-C6>=7.5,(D6-B6)*24-C6<=7.6),7.6,(D6-B6)*24-C6)
I was writing on the smartphone - sorry.
You are so awesome! Thank you!! One more thing (I am being a pain in the a**e, I know) on reflection, to get the total hours to be 38, can it be a max of 7.6 hours for the day. So if they work 10 hours it will only show 7.6hours, but if they work 3hrs it will show 3 hours.
Heaps sorry for screwing you around.
Then why you mentioned the below in Cell I6
"When they work overtime I want it to show the hours they worked"
Sorry about the confusion. On reflection, I need the cell to be a maximum on 7.6 (even if they work 10hrs) but if they finish early I would like it to say the hours worked. That way I can add the two columns together, Leave and Hours worked, and it will total 38 hrs.
E6=IF(MOD(D6-B6,1)*24-C6=7.5,7.6,MOD(D6-B6,1)*24-C6)
Try this and copy towards down
Again - reading and understanding is the key. instead of and(les than 7.6 more than 7.5) use just: more than 7.5
Formula:=IF(OR(B6="",D6=""),"",if((D6-B6)*24-C6>=7.5,7.6,(D6-B6)*24-C6)
You are a LEGEND!!!! Thank you so much!!!!!!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks