Change Timestamp

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”.
CI time
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"


The “12/30/1899” is still there.

Is your expression giving the wrong result?

Yes, it is giving the wrong result.

1 Like

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

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.



1 Like

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:

2 Likes

Thank you for the help. It is working.

1 Like