Subtract lunch break minutes

[Fabio_Moreno]

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.

[W_Vann_Hall]

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.

[Fabio_Moreno]

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

[W_Vann_Hall]

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

[Fabio_Moreno]

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

[Fabio_Moreno]

W_Vann_Hall:

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},

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!

[Justin_Barrett]

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.

[W_Vann_Hall]

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

[Fabio_Moreno]

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

[Justin_Barrett]

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