Learning to use IFS to condition Overtime calculation

[mingming]

Hi all, I'm trying to add more condition to calculate [Overtime].

Ultimately, I want to consider time checked in before 8:10am or checkouts later than 6.50pm as Overtime. However, in the event that both happen then [Overtime] should be SUM of checked time before 8:10am and checkouts later than 6.50pm.

I'm trying to set the formula below, but Appsheet is not accepting it and I'm trying to figure out where went wrong?

IFS(

ISNOTBLANK([Check Out Date Time]), True,
TIME([Check In Date Time]) < "08:10:00", "09:00:00" - TIME([Check In Date Time]),
TIME([Check Out Date Time]) > "18:50:00",TIME([Check Out Date Time]) - "18:00:00",

AND(TIME([Check In Date Time]) < "08:10:00",TIME([Check Out Date Time]) > "18:50:00"),("09:00:00" - TIME([Check In Date Time])) + (TIME([Check Out Date Time]) - "18:00:00")

)

[Suvrutt_Gurjar · Accepted solution]

To start with, could you please try if the following works

IFS(

TIME([Check In Date Time]) < "08:10:00",  "09:00:00" - TIME([Check In Date Time]),
TIME([Check Out Date Time]) > "18:50:00",TIME([Check Out Date Time]) - "18:00:00",

AND(TIME([Check In Date Time]) < "08:10:00",TIME([Check Out Date Time]) > "18:50:00"),("09:00:00" - TIME([Check In Date Time])) + (TIME([Check Out Date Time]) - "18:00:00")

)