Forum Discussion

Martin DeTerwangne's avatar
Martin DeTerwangne
Copper Contributor
Mar 01, 2018

Calculated field

Hi all,

I would like to create a Status column on a list. It would give a status IN or OUT depending on conditions : 
If there is no checkOutTime, it has to check if the checkin datetime is today. If it isn't then, status should be OUT. If it is today then it should be IN. 
If CheckOutTime is filled in, then status is OUT. 

I wrote this formula in the calculated column properties : 
=IF(CheckOutTime="",IF(DATEDIF([Checkin DateTime],CheckOutTime,"d")>0,"OUT","IN"),"OUT")

But it doesn't seem to work.

 

Could you help me ? 

Thanks !! 

Martin

SharePoint Online

5 Replies

  • JessicaWorkman's avatar
    JessicaWorkman
    Copper Contributor
    Jan 31, 2019

    Hi

     

    I was looking to do something like this yesterday so I thought I'd come back to comment here after it worked. 

     

    Now() did not work for me but using [Today] did work in a calculated field in a SharePoint custom list. 

  • John Aage Andersen's avatar
    John Aage Andersen
    Iron Contributor
    Mar 02, 2018

    Try and replace your usage of Today with NOW() in your calculation columns! That works for me :)

    Kind regards, John

  • Deleted's avatar
    Deleted
    Mar 01, 2018

    I tried to do what you wanted to do but without the today function and a way to update it it won't work.

     

    I would suggest a creating flow to do it.

     

    Run the flow on a schedule, every hour as the trigger or what ever is needed

    In the flow do the following:

    1. Get items (List to check)

    2. Apply to each (Value from List)

    3. Add a condition using advanced mode: @empty(items('Apply_to_each')?['CheckoutTime'])

    4a. In the Yes Condition put another condition that compares utchNow() is greater than Checkin DateTime

    4b. In the No Condition put and Update item and set the status column to OUT

    5a. In Yes of 4a. Condition add update item and set the Status column to IN

    5b. In No of 4a. Condition add update item and set the Status column to OUT

     

    I created this to test if it would work and it seems to work quite well.

     

     

     

     

    • Paul Hunt - Cimares's avatar
      Paul Hunt - Cimares
      MVP
      Mar 01, 2018

      Hi Martin,

       

      I don't believe calculated columns are able to support the TODAY function, so there's no easy way of comparing the date in the column with todays date.

       

      Paul.