Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Formula: time worked

Topic Labels: Formulas
Solved
331 3
cancel
Showing results for
Did you mean:
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
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>>
)
``````
3 Replies 3
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>>
)
``````
4 - Data Explorer

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!!

14 - Jupiter

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: