VB

[LA_MERC_LaTech]

This is what I'm looking at so far,

Public Function nestedif(C27, C28, D27, D28)
If D27 = "" Then
If C27 = "" Then
E27 = ""
Else: E27 = (12 + D28) - C27
Else: E7 = ((12 + D28) - (12 + C28) + (D27 - C27))
End Function

[LA_MERC_Onji]

did i catch a niner in there?

[LA_MERC_LaTech]

What do I name E27...the target cell

Public Function nestedif(In1, Out1, In2, Out2)
If In2 = "" Then
If In1 = "" Then
E27 = ""
Else: E27 = (12 + Out2) - In1
Else: E7 = ((12 + Out2) - (12 + In2) + (Out2 - Out1))
End Function

[LA_MERC_DocSparky]

I don't know exactly what you are doing, but here is the type of code you would use:


Public Function CalcHours(in1, out1, in2, out2)
If IsNull(in1) Then
If IsNull(out1) Then
CalcHours = Null
Else
CalcHours = (12 + out2) - in1
End If
Else
CalcHours = ((12 + out2) - (12 + in2) + (out2 - out1))
End If
End Function

To return the value, use the function name as the variable.

You would then call the function from the worksheet as "=CalcHours(C27,D27,C28,D28)" or whatever cells are used and put the function in E27

[LA_MERC_LaTech]

Presently, the code that is working (kinda) is

=IF(D27="",(IF(C27="", "", SUM(12+D28)-C27)),SUM((12+D28)-(12+C28)+(D27-C27)))

I can see how your bit works...didn't know there was a IsNull F(x) already built in

[LA_MERC_DocSparky]

In the cell formula, you use C27="". In the VB code, you have to see if the variable is empty because otherwise it will be a number. The IsNull is just a function to see if a value was passed in the cell (a number being different than a string).

Regardless, building functions can be useful if you need more complex code. Using the in cell Nested IFs is fine, but they break down at some point. For instance if you have to put in logic on each parameter to see if it is 1am or 1pm. In the VB code, you could modify the values and then do the calculations. Anyway... just another tool that you can use.

Definitely a lot of niners in there.

[LA_MERC_LaTech]

Public Function Hours(In1, Out1, In2, Out2)
If IsNull(In1) Then
If IsNull(Out1) Then
Hours = ""
Else
Hours = (12 + Out2) - In1
End If
ElseIf Out1 > 12 Then
Hours = ((In1 + In2) + (Out2 - Out1))
Else
Hours = ((12 + Out2) - (12 + In2) + (Out2 - Out1))
End If
End Function

Do I need another EndIf after the elseif?

[LA_MERC_DocSparky]

No, I think you are good there. Indenting helps the readability though:


Public Function Hours(In1, Out1, In2, Out2)
If IsNull(In1) Then
If IsNull(Out1) Then
Hours = ""
Else
Hours = (12 + Out2) - In1
End If
ElseIf Out1 > 12 Then
Hours = ((In1 + In2) + (Out2 - Out1))
Else
Hours = ((12 + Out2) - (12 + In2) + (Out2 - Out1))
End If
End Function

[LA_MERC_LaTech]

Yeah, it's indented in VB...when I paste it, garbles it up.

[LA_MERC_th33_r00k]

I think you just need one of these:

http://www.clockway.com/mm5/merchant.mvc?Screen=PROD&Store_Code=C&Product_Code=CHM620F314&Category_Code=wallclocks1190p

....Niner!

[LA_MERC_LaTech]

Public Function Hours(In1, Out1, In2, Out2)
If Out1 = "" Then
If In1 = "" Then
Hours = "0"
Else
Hours = (12 + Out2) - In1
End If
ElseIf Out1 = 12 Then
Hours = (Out1 - In1) + (Out2 - In2)
ElseIf Out1 < 12 Then
Hours = ((12 + Out1) - In1) + (Out2 - In2)
Else
Hours = (Out2 - In2) + ((12 + Out1) - (In1))
End If
End Function

Doc...this works for no matter when you take a lunch, if you don't take a lunch or if you leave whenever. Thanks again for all your help.