Share via

Why isn't my IF formula for categorizing dates by quarter working in Microsoft Lists?

Anonymous
2025-01-09T19:21:39+00:00

Hello,
I am trying to use an IF firmula in Microsoft Lists to categorize a date (in the [Data de Término] columm) into quarter for the year 2025, bit it isn't working as expected. The [Data de Término] column is of the Date and Time type Here is the formula I am using:

=IF(YEAR([Data de Término])<2025, "Past", IF(YEAR([Data de Término])=2025; IF(MONTH([Data de Término])<=3, "1st Quarter / 2025"; IF(MONTH([Data de Término])<=6, "2nd Quarter / 2025", IF(MONTH([Data de Término])<=9, "3rd Quarter / 2025", IF(MONTH([Data de Término])<=12, "4th Quarter / 2025", "Broken")))); "Future"))

If the year of [Data de Término] is before 2025, the formula should return "Past".

  • For the year 2025, the formula should categorize the date into one of the following quarters:
    • 1st Quarter / 2025 for January to March
    • 2nd Quarter / 2025 for April to June
    • 3rd Quarter / 2025 for July to September
    • 4th Quarter / 2025 for October to December
  • For any year after 2025, the formula should return "Future".
  • If the date does not match any of the above conditions, it should return "Broken".
  • When I try to use this formula in Microsoft Lists, it doesn't seem to work as expected.
  • The [Data de Término] column is set to Date and Time, and I am not sure if the presence of time information is affecting the formula.
  • I have tested similar logic in Microsoft Excel, and it works fine, but the formula does not seem to evaluate correctly in Microsoft Lists.

Could you please help me understand why this formula isn't working as expected and how I can adjust it to work with the Date and Time column type in Microsoft Lists?

Thank you!"

Microsoft 365 and Office | SharePoint | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-01-23T13:42:14+00:00

    Unfurtunately not

    I get the answer: "Something got wrong, The formula is not supported or contains a syntax error."

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-01-15T01:14:34+00:00

    Dear Débora,

    Have you tried the suggestions above? May I know whether you need further help on this issue?

    Your effort and time are greatly appreciated!

    Sincerely

    Cliff | Microsoft Community Moderator

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-01-14T07:32:49+00:00

    Dear Débora,

    Thanks for your updates.

    Sorry for the confusion.

    I think it is a publish mistake of the page.

    I just copy and paste the formula from my environment.

    =IF(YEAR([Data de Término])<2025,"Past",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=3),"1st Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=6),"2nd Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=9),"3rd Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=12),"4th Quarter / 2025",IF(YEAR([Data de Término])>2025,"Future","Broken"))))))

    Image

    Can you see that correctly now from your side? I found that < will be replaced with < and > will be replaced with >. Could you copy the formula in Word and use find and replace to correct &It; as < and try it to see the result?

    Welcome to share any updates at your convenience.

    Your effort and time are greatly appreciated!

    Sincerely

    Cliff | Microsoft Community Moderator

    Was this answer helpful?

    0 comments
  4. Anonymous
    2025-01-13T19:57:26+00:00

    May I use "&LT" or is It a mistake when u published gere?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2025-01-10T05:07:57+00:00

    Dear Débora,

    I think that you can try the following formula to see and let us know the result.

    =IF(YEAR([Data de Término])<2025,"Past",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=3),"1st Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=6),"2nd Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=9),"3rd Quarter / 2025",IF(AND(YEAR([Data de Término])=2025,MONTH([Data de Término])<=12),"4th Quarter / 2025",IF(YEAR([Data de Término])>2025,"Future","Broken"))))))

    Your effort and time are greatly appreciated!

    Sincerely

    Cliff | Microsoft Community Moderator

    Was this answer helpful?

    0 comments