Help

Formula: time worked

Topic Labels: Formulas
Solved
Jump to Solution
813 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jennifer_Melo
4 - Data Explorer
4 - Data Explorer

Hi, I need some help, I am counting the total hours worked of my employees, But I need a formula that won’t count the lunch time or that some how if table (lunch in) or (lunch out) is empty it won’t say NaN. some people take lunches, some don’t this is what I have it like this.
I have it to count hours worked before lunch and after lunch and add that all up. Screen Shot 2020-10-19 at 1.25.01 PM|700x99

1 Solution

Accepted Solutions
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi, @Jennifer_Melo!
In both your hours before lunch and hours after lunch formulas, make the output dependent on the presence of a value in the Lunch out and Lunch in fields. Just wrap the whole formula you currently have in a conditional, like this:

IF(
   {Lunch out},
   <<your existing hours before lunch formula>>
)
IF(
   {Lunch in},
   <<your existing hours after lunch formula>>
)

Then, in your Total hrs Worked formula, use a different set of arithmetic based on the presence or lack of values in those fields:

IF(
   AND({Lunch out}, {Lunch in}),
   <<formula to add hours from those two fields>>,
   <<formula to find hours between {Time in.} and {Time out.} instead>>
)

See Solution in Thread

3 Replies 3
Jeremy_Oglesby
14 - Jupiter
14 - Jupiter

Hi, @Jennifer_Melo!
In both your hours before lunch and hours after lunch formulas, make the output dependent on the presence of a value in the Lunch out and Lunch in fields. Just wrap the whole formula you currently have in a conditional, like this:

IF(
   {Lunch out},
   <<your existing hours before lunch formula>>
)
IF(
   {Lunch in},
   <<your existing hours after lunch formula>>
)

Then, in your Total hrs Worked formula, use a different set of arithmetic based on the presence or lack of values in those fields:

IF(
   AND({Lunch out}, {Lunch in}),
   <<formula to add hours from those two fields>>,
   <<formula to find hours between {Time in.} and {Time out.} instead>>
)

This worked out perfect, thank you so much, I really appreciate. I’m new to all this, so I’m excited to learn more. Thank you!!

That being the case, I’ll give you a quick explanation as to why this worked the way it does.

The IF() function looks at the first argument passed in (the “arguments” of a function are the things separated by commas inside the function’s parentheses) and tries to determine if that argument resolves to a TRUE() value or a FALSE() value. Something simple like 2 = 2 would resolve to TRUE(), and 2 = 3 would resolve to FALSE().

But what does passing in the name of a Field that doesn’t have a simple math value, or checkbox value do? Quite simply, if the field has a value (a Date, some Text, a Number other than 0, a Single Select option), any value at all in it, that will evaluate to TRUE(). If the field is empty, there is not value whatever, that is called a null field, and it resolves to FALSE().

So the first formula I posted above will resolve to FALSE() if there is no value whatever in the {Lunch out} field, and it will resolve to TRUE() if there is any value at all in there. If it resolves to TRUE(), it will evaluate the second argument in the argument list – in this case, your formula for determining hours before lunch.

If a the first argument resolves to FALSE(), then the IF() function evaluates the third argument in the argument list. If there is no third argument, then the formula just stops, and nothing is output into the field – ie, it’s a null field. In the case of the third formula I posted above, though, there is a third argument in the list of arguments, an alternative formula to evaluate – so that gets evaluated instead of the second one.

A bit wordy… but hopefully it helps clarify what is going on there, and helps you understand how you can use these to open up more powerful workflows for yourself :slightly_smiling_face: