Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formula: time worked

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1385
3

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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>>
)
```

Reply

3 Replies 3

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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>>
)
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 19, 2020 01:50 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Reply