Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Subtract lunch break minutes

Topic Labels: Formulas
2378 10
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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.

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

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

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

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?

image.png

very appreciated all the help, thank you!

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.

Screen Shot 2019-04-02 at 3.09.45 PM.png

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.

@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.

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

this is what i get:
Screenshot_36

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

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

59%20PM

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