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.
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.
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:
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
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'
)
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
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!
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.
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.
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
Apr 03, 2019 03:17 PM
You have to open the Formatting tab when customizing the field. Change the format to Duration, and it will work.
Apr 03, 2019 09:51 PM
it works now! thank you so much for the help!