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
Oct 19, 2020 01:27 PM
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
Solved! Go to Solution.
Oct 19, 2020 01:36 PM
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>>
)
Oct 19, 2020 01:36 PM
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>>
)
Oct 19, 2020 01:50 PM
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!!
Oct 19, 2020 01:59 PM
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: