Hello,
I have a timesheet app and i am wondering if i can limit the input on the minutes to be ending by 0s or 5s. What I mean is that instead of an employรฉe saying he started at 08:02 I want that it is limited to 08:00 or 08:05. I already disabled the seconds. Thanks in advance
hello i tried to apply this formula in my app but i get an error parameter 1 of function minute is of the wring type. From what i see it is the format of my column but i do not understand where i am wrong.
You wonโt be able to us ethat perfectlyโฆ itโs for a duration columnโฆ you will need to ADAPT it to fit your needs.
@LeventK Can you help out, I haven;t done this in awhile!
@Andrian_Andreev
Assuming that your column type is TIME, try with this expression:
IF(
MOD(MINUTE([TIME] - "00:00:00"),5) = 0,[TIME],
IF(
(CEILING(MINUTE([TIME] - "00:00:00") / 5) * 5) + 5 = 60,
TIME(HOUR([TIME] - "00:00:00") + 1 & ":00"),
TIME(HOUR([TIME] - "00:00:00") & ":" & ((CEILING(MINUTE([TIME] - "00:00:00") / 5) * 5) + 5))
)
)
@Andrian_Andreev
You can also implement and use the above expression as an initial value of your TIME column:
IF(
MOD(MINUTE(TIMENOW() - "00:00:00"),5) = 0,[TIME],
IF(
(CEILING(MINUTE(TIMENOW() - "00:00:00") / 5) * 5) + 5 = 60,
TIME(HOUR(TIMENOW() - "00:00:00") + 1 & ":00"),
TIME(HOUR(TIMENOW() - "00:00:00") & ":" & ((CEILING(MINUTE(TIMENOW() - "00:00:00") / 5) * 5) + 5))
)
)
it is not working. The formula is accepted but i think the problem is here : i replaced your column time by my column witch means begging but the app thinks of the โ00:00:00โ as 12/30/1899 12:00:00 AM.
IF this condition is true:
โฆ(MOD(
โฆMINUTE(
โฆ(The value of column โDebutโ - โ12/30/1899 12:00:00 AMโ)
โฆ5)) is equal to (0)
THEN use the value of:
โฆThe value of column โDebutโ
ELSE use the value of:
โฆIF this condition is true:
โฆ(((CEILING(
โฆ(MINUTE(
โฆ(The value of column โDebutโ - โ12/30/1899 12:00:00 AMโ) / 5) * 5 + 5) is equal to (60)
โฆTHEN use the value of:
โฆTIME(
โฆThe concatenation of all these text values (
โฆ(HOUR(
โฆ(The value of column โDebutโ - โ12/30/1899 12:00:00 AMโ) + 1
โฆ":00")
โฆELSE use the value of:
โฆTIME(
โฆThe concatenation of all these text values (
โฆHOUR(
โฆ(The value of column โDebutโ - โ12/30/1899 12:00:00 AMโ)
โฆ":"
โฆ((CEILING(
โฆ(MINUTE(
โฆ(The value of column โDebutโ - โ12/30/1899 12:00:00 AMโ) / 5) * 5 + 5)
โฆ
User | Count |
---|---|
44 | |
29 | |
24 | |
21 | |
13 |