Hi, I would like to know if my staff is late or not just by forming a formula with one of my columns. I have a column called โTime Inโ and the column type is Change Timestamp. Using this โTime Inโ column, I would like to form a formula under the โLateโ column to know if the staff is late for work.
Letโs assume that the start work timing is 8.30AM.
The formula that I have put in โLateโ column is:
IF(AND(ISNOTBLANK([Time In]),[Time In] > TIME(โ8:30โ)),โYESโ,โNOโ)
However, if my โTime Inโ column shows โ5/13/2021 1:11:03โ, the โLateโ column will show โYESโ.
In the above image, it shows โThe value of column โTime Inโ) is greater than (TIME(โ12/30/1899 8:30:00 AMโ))โ
Why is there a 12/30/1899?
Is there any way to solve this?
Thank you:)
Should be
TIME( [Time In] ) > "08:30:00"
Is your expression giving the wrong result?
Yes, it is giving the wrong result.
Please post a screenshots of the configuration screens for the Time In and Late columns that include the top section, the Type Details section, and the Auto Compute section.
Time In is a ChangeTimestamp, which is a DateTime value. Both "08:30"
and anything returned by TIME() is a Time value. To compare a DateTime value with a Time value, AppSheet converts the Time value to a DateTime value by adding a Date component. The default Date component is the date you see.
The easiest fix for you problem is to use this App formula expression for Late instead:
IF(
AND(
ISNOTBLANK([Time In]),
(TIME([Time In]) > "08:30:00")
),
"YES",
"NO"
)
See also:
Thank you for the help. It is working.
I have not for once used columns of โchangetimestampโ type, so I really can not comment on that.
But what you are trying to achieve can be perfectly done by using the type of the same column as โDateTimeโ or simply โTimeโ.
Use that and see if the situation changes
User | Count |
---|---|
39 | |
35 | |
29 | |
23 | |
18 |