none
Attempting to get a Calculated Column to output Yes/No (Formula)

    Question

  • I’mtrying to make a calculated column produce a Yes/No when:

     

    IF([Reminder Date]= [Today], “Yes”)

     

    and

     

    IF([Reminder Date] does not equal [Today], “No”)

    What is the correct formula?

    Monday, February 11, 2019 11:52 PM

Answers

  • Hi,

    You could use the formula as below.

    =IF([Reminder Date]=TODAY(),"Yes","No")


    And the result as below.


    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Proposed as answer by ME_77 Tuesday, February 12, 2019 5:01 AM
    • Marked as answer by else111 Wednesday, February 13, 2019 4:42 PM
    Tuesday, February 12, 2019 4:44 AM
    Moderator

All replies

  • I think you are running into two issues with what you are trying to do.

    1) Calculated columns only calculate when you create or modify an item. So if the record was modified yesterday and the reminder date is today the calculated column will still show a "No" because the last time it was calculated was Yesterday.

    2) Using Equals with DateTime fields is almost always a problem because Reminder Date and Today will contain both a date and a time.  Even Columns that are flagged as Date only store a date and a time.  So the DateTime needs to match exactly all the way down to the millisecond.  That probably isn't going to happen.  Use <= instead.  That would tell you when the Reminder Date has passed.


    Paul Stork SharePoint Server MVP
    Owner/Principal Architect: Don't Pa..Panic Consulting
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as 'answered' if this solves your problem.

    Tuesday, February 12, 2019 3:32 AM
  • That's right, calculated field only refresh when some item is updated.

    Justin Liu Office Apps & Services MVP, MCSE
    Senior Software Engineer
    Please Vote and Mark as Answer if it helps you.

    Tuesday, February 12, 2019 3:37 AM
  • Hi,

    You could use the formula as below.

    =IF([Reminder Date]=TODAY(),"Yes","No")


    And the result as below.


    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    • Proposed as answer by ME_77 Tuesday, February 12, 2019 5:01 AM
    • Marked as answer by else111 Wednesday, February 13, 2019 4:42 PM
    Tuesday, February 12, 2019 4:44 AM
    Moderator
  • Hi else111,    

    If the reply is helpful to you, you could mark the reply as answer. Thanks for your understanding.

    Best regards,

    Sara Fan

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Tuesday, February 12, 2019 9:19 AM
    Moderator