Greetings everybody,
I am using this formula in Cell 'H8' to calculate overtime in a timesheet.
=IF((OR(E8>="",F8>="")),"",IF((I8="Holiday"),(G8),IF((B8=WEEKDAY(6)),(G8),IF((G8>8),(G8-8),("")))))
This works fine with all the conditions except when total hours=8 hours. When it is 8 hours it returns the result as 8.9E-15. I expect a blank cell when Total hours are 8.
Attached here is the sample.
:thanx:
Re: Overtime Calculation Formula Returns Error Value
Hi
Try formatting the cell as Number
HTH
Robert
Re: Overtime Calculation Formula Returns Error Value
Hi Shree,
Your time math is bringing back a number just slightly more than 8 ie: 8.000000000000001 so I put a round in the If:
=IF(OR(E8>="",F8>=""),"",IF(F8<=E8,ROUND(((F8-E8)*24)+24-1,1),ROUND(((F8-E8)*24-0.5),1)))
Also changed the 2nd formula slightly:
=IF(OR(E8>="",F8>=""),"",IF(OR(I8="Holiday",B8=WEEKDAY(6)),G8,IF(G8>8,G8-8,"")))
Re: Overtime Calculation Formula Returns Error Value
What is
=IF(A1>=""),.....
supposed to do? Am I missing something, because it seems, as I would suspect, to match every condition, be it "", 0, 2, even A. :confused:
Re: Overtime Calculation Formula Returns Error Value
rbrhodes' solution is working perfectly.
Dear ByTheCringe2,
I am very new to Excel. I am not able to explain to your question. I ended up with this formula after a lot of trial and errors. When I used it with out > I got some error which I do not remember.
Thank you all for responding and helping.
Re: Overtime Calculation Formula Returns Error Value
Well, now I've tried it again and it does the same as ="" does. I'm more confused. But it doesn't look like valid Excel, Shree. I would take out all the > and try with just ="" it works for me.
Re: Formula Returns Number With "E" in it
Hello Shree,
Looks like you have a solution but, for what it's worth, I'd try to simplify some of those formulas. This is what I'd use
in B8
=IF(A8="","",TEXT(A8,"ddd"))
in G8
=IF(F8*E8,ROUND(MOD(F8-E8,1)*24-0.5,9),"")
in H8
=IF(G8="","",IF(OR(B8="fri",I8="holiday"),G8,IF(G8<=8,"",G8-8)))
Don’t have an account yet? Register yourself now and be a part of our community!
