Skip to main content

"IF" formula relating to a single select field

  • June 3, 2022
  • 2 replies
  • 12 views
E
Forum|alt.badge.img+7

Hi All,
Very new to AirTable!
I would like assistance on the following:

I would have made a formula for:
if the single select field ‘major/minor’ is Major NC, then give a date 12 weeks from field ‘report date’, if minor NC, do nothing. and I see the correct results showing in that new column that I made. But I would just like the date to show dd/mm/yyyy but I am getting 2022-07-22T00:00:00.000Z for some reason?

Also, it is not showing in the calendar view.

I hope that makes sense!

    2 replies

    Ben_Young1
    Forum|alt.badge.img+22
    • Ben_Young1
    • Brainy
    • 520 replies
    • June 3, 2022

    Hey @Emma_Salter!
    Welcome in!

    Based on what you’ve described, this is something along the lines of what you’re looking for:

    IF(
    {Major/Minor} = "Major NC",
    DATETIME_FORMAT(
        DATEADD(
            {report date},
            12,
            'weeks'
        ),
        'D/MM/YYYY'
    )
)

    With the way this formula is written, if the Major/Minor field has any value except for ‘Major NC’, it will simply return blank.

    For the formatting of the date, we can leverage the DATETIME_FORMAT function, which allows you to specify the desired format using any number of permutations that you can find in this article.
    It’s quite comprehensive, and it’s worth diving into it a bit if you’re curious about what’s possible there.

    Regarding the calendar view…
    I wanted to clarify. Are you trying to only see records on the calendar that reflect records that have a date value return from the formula?

    E
    Forum|alt.badge.img+7
    • Emma_Salter
    • Author
    • Known Participant
    • 11 replies
    • June 3, 2022

    Hey @Emma_Salter!
    Welcome in!

    Based on what you’ve described, this is something along the lines of what you’re looking for:

    IF(
    {Major/Minor} = "Major NC",
    DATETIME_FORMAT(
        DATEADD(
            {report date},
            12,
            'weeks'
        ),
        'D/MM/YYYY'
    )
)

    With the way this formula is written, if the Major/Minor field has any value except for ‘Major NC’, it will simply return blank.

    For the formatting of the date, we can leverage the DATETIME_FORMAT function, which allows you to specify the desired format using any number of permutations that you can find in this article.
    It’s quite comprehensive, and it’s worth diving into it a bit if you’re curious about what’s possible there.

    Regarding the calendar view…
    I wanted to clarify. Are you trying to only see records on the calendar that reflect records that have a date value return from the formula?


    thanks so much for your help!

    I didnt realise there was a formatting tab behind the formula to get rid of the timestamp so that helped too!

    Then by getting rid of the time it now shows in the list for the calendar view so I can view that alongside the current info in calendar!

    What a learning curve I’m on with AT haha


    Terms & ConditionsAccessibility statement