syntax correction

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 14 Feb 2007 18:01 GMT

I wrote this function, the logics seem to ok, but there are too many logic,
as I the result I think I mess up the syntax. Can anyone correct for me?

the function is:
=IF(AND(F8<>"X",F8=""),C3-D8,IF(F8<>"",C3-F8)IF(AND(G8<>"X",G8=""),C3-F8)*IF(G8<>0,C3-G8)*IF(AND(H8<>"X",H8=""),C3-G8,)IF(H8<>"",C3-H8,0))

where C3 is a now() function with customed to time, and where D8 is the
beginning time for an employee. The function above time elapsed from the 1
break to 3 break. Each time a break is entered now() function or C3 has to
minus the time starting a break, then this equal to time elapsed since last
break..thanks

Reply to this Message

Dave F - 14 Feb 2007 18:25 GMT

Maybe:
=IF(AND(F8<>"X",F8=""),C3-D8,IF(F8<>"",C3-F8,IF(AND(G8<>"X",G8=""),C3-F8),IF(G8<>0,C3-G8),IF(AND(H8<>"X",H8=""),C3-G8,IF(H8<>"",C3-H8,0))))))

???

Creating helper columns rather than relying on one long formula may make
your logic clearer.

Dave

Signature

A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

> I wrote this function, the logics seem to ok, but there are too many logic,
> as I the result I think I mess up the syntax. Can anyone correct for me?
[quoted text clipped - 7 lines]
> minus the time starting a break, then this equal to time elapsed since last
> break..thanks

Reply to this Message

Tom Ogilvy - 14 Feb 2007 18:28 GMT

the first part of your IF statement

=IF(AND(F8<>"X",F8=""),C3-D8,IF(F8<>"",C3-F8)

take the And(F8<>"X",F8="") then only thing that satisfys that is if
F8="", so the F8<>"X" is redundant

now that would give you

=IF(F8="",C3-D8,If(F8<>"",C3-F8))

that pretty much boils down to
If(F8="",C3-D8,C3-F8)

Which boils down to

=C3-D8

Although you might have an operator missing between that and the next
statement, it isn't clear, so think you would be better of saying what is
entered in each of your cells and what rules you want to apply to arrive at
what.

Signature

Regards,
Tom Ogilvy

Reply to this Message

Joel - 14 Feb 2007 18:35 GMT

there isn't 1 way to fix the formular. It is not clear what you are trying
to do.

There must be a commar before each IF. * are not allow to nest IF.

Instead you want someting like

if(A=B,if(C=D,if(E=F))) for nesting the IFs

Reply to this Message

Tom Ogilvy - 14 Feb 2007 20:30 GMT

=if(S,if(A,B,C)*if(D,E,F),if(M,N,O)*if(P,Q,R))

would be legitimate, so I am not sure what you mean by * are not allowed.
But you are correct that the formula posted is not clear on what the OP
wants.

Signature

Regards,
Tom Ogilvy

> there isn't 1 way to fix the formular. It is not clear what you are trying
> to do.
[quoted text clipped - 16 lines]
> > minus the time starting a break, then this equal to time elapsed since last
> > break..thanks

Reply to this Message