Change Timestamp

lala_land · 05-12-2021 10:25 AM

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:)

Marc_Dillon

Should be
TIME( [Time In] ) > "08:30:00"

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

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

Steve

Is your expression giving the wrong result?

lala_land

Yes, it is giving the wrong result.

Steve

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.

lala_land

Steve

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"
)