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

)

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

)

[mingming]

Thanks for the reply Suvrutt, I will try again and see how it works as I am unable to backdate now..

App is able to input the code above 👍

[Suvrutt_Gurjar · Accepted solution]

You are welcome and thank you for the update.

I believe you were getting error because the result of the first "condition-value"  pair in your expression, that is the part

ISNOTBLANK([Check Out Date Time]), True 

was inconsistent with other condition-value pairs. The next three condition-value pairs are evaluating to a "duration" type whereas the first result was evaluating to a TRUE/FALSE which was giving error.

So I suggested an expression without that part. However, you may want to mention why that part was added and we could explore if it is indeed required.

[mingming]

I included ISNOTBLANK([Check Out Date Time]), True 

Is because I want to deactivate [Overtime] field or in other words to prevent system from considering / generate values for [Overtime] field.

[Check Out Date Time] is only generated when a user click on an action to record Check Out from the attendance system.

There are chances where a user check in but forgot to check out, therefore I intend to leave the [Check Out Date Time] field blank instead of fixing a cut-off time for it.

Due to not all users are checking out at the same time, and there might be overtime for certain users, therefore it is not possible to auto clock out at a fixed time at the moment

[Suvrutt_Gurjar]

Thank you for the update.

In that  case you could possibly try an expression of 

IF( ISBLANK([Check Out Date Time]) , "000:00:00",

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

             )

     )

The above will mean, if [Check Out Date Time] is blank, the [Overtime] duration will always be 0 or else it will be calculated as per the logic.

As a sidenote, If you are using the AppSheet app for time recording by different users on different devices, please be aware of the following discussion.

Prevent timestamp manipulation? - Google Cloud Community