This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

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
- Subtract lunch break minutes

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

0
2661
10

Showing results for

Comment Post Options

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

Mar 03, 2019 12:22 PM

hello everyone! i have this table that clocks in and clocks out people at work, but i would like that it would subtract 30 minutes(lunchbreak)

so the person would clock-in then later clock-out and at clockout would subtract -30mins

this is what i currently have:

Přihlášen = clockin in

Odhlásit = clock out

.

IF(

AND(NOT({Přihlášen}), NOT({Odhlásit})),

“0”,

IF(

{Odhlásit},

ROUND( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘hours’), 60 ) & " hrs " &

ROUND( MOD( DATETIME_DIFF({Odhlásit}, {Přihlášen}, ‘minutes’), 60 ), 0 ) & " min"

,

ROUND( DATETIME_DIFF( NOW(), {Přihlášen}, ‘hours’), 0) & " hrs " &

ROUND( MOD( DATETIME_DIFF( NOW(), {Přihlášen}, ‘minutes’), 60), 0) & " min"

)

)

any help would be apreciated, thank you.

10 Replies 10

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

Mar 04, 2019 08:53 AM

Well, the easiest way would be to replace `{Odhlásit}`

in your formula with `DATEADD({Odhlásit},-30,'minutes')`

, shortening the total duration by a half-hour. However, i suspect you’ll need to make the overall algorithm a but more robust, providing some mechanism to allow exception processing: Consider these situations:

- As a one-time deal, Employee A arranges with his or her supervisor to work only four hours and leaves before lunch.
- Employee B clocks in, is suddenly stricken with an upset stomach, and clocks out 15 minutes later.
- Employee C is hired for an evening shift position, with the explicit understanding he or she will not take a meal break.
- Employee D is hired for a split-shift position, working from 11 am to 7 pm. Because of coverage requirements, he or she is expected to take both a midday and an evening break.

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

Mar 04, 2019 10:46 AM

Thank you so much for your reply.

Well i dont know much about formulas, we have only 1 shift.

Is it possible to add something like if hours equals or bigger than 4hours then -30mins?

Thanks again for ur help

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

Mar 04, 2019 08:11 PM

OK, here’s an expanded, slightly restructured version of your formula that shortens the elapsed time by 30 minutes for all measured elapsed times equal to or greater than 4 hours’ duration. If both `{Přihlášen}`

and `{Odhlásit}`

are set, time is measured from `{Odhlásit}`

to `{Přihlášen}`

, subject to that possible 30-minute reduction; if only `{Odhlásit}`

is set, measured time is that between `{Odhlásit}`

and `NOW()`

.

You can mark, copy, and paste the entire formula — indentations included — into the formula configuration box of a formula field; Airtable will strip out the indentations and formatting correctly.(In addition, the block of text has been tagged as ‘code’, so single and double quotes maintain the proper encoding.)

```
IF(
{Přihlášen},
ROUND(
DATETIME_DIFF(
IF(
{Odhlásit},
IF(
DATETIME_DIFF(
{Odhlásit},
{Přihlášen},
'hours'
)>=4,
DATEADD(
{Odhlásit},
-30,
'minutes'
),
{Odhlásit}
),
IF(
DATETIME_DIFF(
NOW(),
{Přihlášen},
'hours'
)>=4,
DATEADD(
NOW(),
-30,
'minutes'
),
NOW()
)
),
{Přihlášen},
'hours'
),
0
)&" hrs "&
ROUND(
MOD(
DATETIME_DIFF(
IF(
{Odhlásit},
IF(
DATETIME_DIFF(
{Odhlásit},
{Přihlášen},
'hours'
)>=4,
DATEADD(
{Odhlásit},
-30,
'minutes'
),
{Odhlásit}
),
IF(
DATETIME_DIFF(
NOW(),
{Přihlášen},
'hours'
)>=4,
DATEADD(
NOW(),
-30,
'minutes'
),
NOW()
)
),
{Přihlášen},
'minutes'
),
60
),
0
)&" min"
)
```

I made one material change from your version: If `{Přihlášen}`

has not been set, the routine returns a `BLANK()`

field rather than `'0'`

. I find this a more aesthetically pleasing response, personally — but if you wish to return to the original functionality, replace the last two lines in the formula with these three:

```
)&" min",
'0'
)
```

Reply

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

Mar 10, 2019 07:55 AM

Hello, only now i had the time to come here again.

just wanted to say how much i appreciated your help!

thanks a lot! its working perfectly

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

Apr 02, 2019 12:20 PM

so i decided to continue here looking for help instead of opening a new thread, since its to continue with this table.

so i wanted to add the total sum of hours. i tried in the summary, but there is no SUM option.

Is there a way to see the total worked hours from the last column?

very appreciated all the help, thank you!

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

Apr 02, 2019 01:17 PM

One option is to add another formula field that extracts the time data and presents it in a way such that Airtable will let you format the field as a duration. That way the column will summarize the durations correctly.

Here’s the formula I used:

```
((VALUE(LEFT({Hours Worked}, FIND(" hrs ", {Hours Worked}) -1), 2) * 60)
+ VALUE(MID({Hours Worked}, FIND(" hrs ", {Hours Worked}) + 5, 2))) * 60
```

Another option is to rework the original formula to output a duration-compatible number in the first place. You lose the “friendly” labeling, but it makes summing the values a breeze.

Reply

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

Apr 02, 2019 02:15 PM

@Justin_Barrett has the answer.

And before you ask :winking_face: , calculating the sum of hours and minutes worked in a way that can then be ‘friendly formatted’ is a *much* more complicated task.

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

Apr 03, 2019 11:47 AM

Thank you for your help, unfortunately it donst seem to work on mine.

this is what i get:

its seems to work in yours though, i apreciate your help

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

Apr 03, 2019 03:17 PM

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

Apr 03, 2019 09:51 PM

it works now! thank you so much for the help!