I've been struggling with this for a few days now and wondered if anyone could help!

I have a SharePoint list (attendance tracking) with 4 date columns:

  • Arrival Time (this is filled-in using [Today] by default when the item is created)
  • Sign-out
  • Sign-in
  • Exit Time

And a text column called Status.

I want the Status column to quickly show whether the person is in the building, or out, based on the 4 date columns (e.g. if there is a time/date in the Arrival Time column, but not in Sign-out column then they are in the building.

I am trying to adapt some json on GitHub but I am really struggling to get the if statements down!

This is ideally what I want to achieve: enter image description here

Any help would be greatly appreciated!

  • Does your logic and screenshot match? For example, in the 2nd row, Sign-out column has a value, but status is "In" Sep 25, 2020 at 15:12
  • The way we use it is a person will arrive (Arrival Time), they might sign-out for a break (Sign-Out Time) and then sign back in (Sign-In Time), then they will leave at the end of they day (Exit Time). So each row is their sign-in's and out's for the day!. On the second row they have signed back in, so they are In (Status) the building.
    – B4N3
    Sep 25, 2020 at 15:34

1 Answer 1


Try the following:

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(Number([$SignOutTime])!=0 && Number([$ExitTime])!=0, 'Out', if(Number([$SignOutTime])!=0 && Number([$SignInTime])==0,'Out',if(Number([$ArrivalTime])==0, 'Out', 'In')))",
  "attributes": {
    "class": "=if(Number([$SignOutTime])!=0 && Number([$ExitTime])!=0,'sp-field-severity--severeWarning',if(Number([$SignOutTime])!=0 && Number([$SignInTime])==0,'sp-field-severity--severeWarning',if(Number([$ArrivalTime])==0, 'sp-field-severity--severeWarning', 'sp-field-severity--good')))"

enter image description here

  • This is amazing, thank you so much! It has totally saved my week, and helped me to understand logic better!
    – B4N3
    Sep 28, 2020 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.