As is, the formula below works perfect for my project.

However, I would like to spice it up by having the results just display the Month and the Year of the "Next Test Date". Also, is there a way to avoid adding by days (example "+365" or "+182") and just add by months??

working with SharePoint Online if that helps.

I assume I need the following added but I dont know how to merge it to the current Formula.

=TEXT([Created],” (MMM YYYY)”)

Current working Formula:

=IF(OR(AND([Mil Status]="AGR",[Composite Score]>=90),AND([Mil Status]="DSG",[Composite Score]>=75)),[Previous Test Date]+365,(IF(OR(AND([Mil Status]="AGR",[Composite Score]>=75),AND([Mil Status]="AGR",[Composite Score]<=89)),[Previous Test Date]+182)))

1 Answer 1


Try using this formula:

=IF(OR(AND([Mil Status]="AGR",[Composite Score]>=90),AND([Mil Status]="DSG",[Composite Score]>=75)),TEXT([Previous Test Date]+365, "MMM YYYY"),(IF(OR(AND([Mil Status]="AGR",[Composite Score]>=75),AND([Mil Status]="AGR",[Composite Score]<=89)),TEXT([Previous Test Date]+182, "MMM YYYY"))))

Also, you can add months & years in date as well.

Example: Adding 3 months to [Previous Test Date].

=DATE(YEAR([Previous Test Date]),MONTH([Previous Test Date])+3,DAY([Previous Test Date]))

Check below documentation for more information & syntax:

Calculated Field Formulas

  • 1
    You Sir! are an amazing person!! This works perfect, thank you so much. I will also play around with that other bit you added after I read up on the provided link. Commented Nov 18, 2021 at 6:41
  • 1
    Great, glad it worked for you Billy! Commented Nov 18, 2021 at 6:44
  • **Update on that formula.. Here is the revised Formula with the assistance of "Ganesh" =IF(OR(AND([Mil Status]="AGR",[Composite Score]>=90),AND([Mil Status]="DSG",[Composite Score]>=75)),TEXT([Previous Test Date]+365,"MMM YYYY"),(IF(AND([Mil Status]="AGR",[Composite Score]>=75),TEXT([Previous Test Date]+182,"MMM YYYY"),(IF(OR(AND([Mil Status]="AGR",[Composite Score]<=74),AND([Mil Status]="DSG",[Composite Score]<=74)),TEXT([Previous Test Date]+0,"MMM YYYY")))))) Commented Nov 18, 2021 at 11:48

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.