 # Formula Returns Number With "E" in it

• 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:

## Files

• 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 &quot;E&quot; 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)))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!