# Subtract lunch break minutes

#1

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.

0 Likes

#2

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:

1. As a one-time deal, Employee A arranges with his or her supervisor to work only four hours and leaves before lunch.
2. Employee B clocks in, is suddenly stricken with an upset stomach, and clocks out 15 minutes later.
3. Employee C is hired for an evening shift position, with the explicit understanding he or she will not take a meal break.
4. 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.
0 Likes

#4

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

0 Likes

#5

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,
{Odhlásit},
-30,
'minutes'
),
{Odhlásit}
),
IF(
DATETIME_DIFF(
NOW(),
{Přihlášen},
'hours'
)>=4,
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,
{Odhlásit},
-30,
'minutes'
),
{Odhlásit}
),
IF(
DATETIME_DIFF(
NOW(),
{Přihlášen},
'hours'
)>=4,
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'
)
``````
1 Like

#6

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

0 Likes

#7

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!

0 Likes

#8

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.

1 Like

#9

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

0 Likes

#10

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

0 Likes

#11

You have to open the Formatting tab when customizing the field. Change the format to Duration, and it will work.

0 Likes

#12

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

0 Likes