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.
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.
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
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.)
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'
)
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.)
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
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.)
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'
)
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!
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.
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.
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?
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.
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.
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.
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
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
You have to open the Formatting tab when customizing the field. Change the format to Duration, and it will work.
You have to open the Formatting tab when customizing the field. Change the format to Duration, and it will work.
