0

Hope you can help me on this issue.

I created a calculated column with the following formula to auto calculate the "Fiscal Quarter" based on the value in the "Date Of Review" column in my list.

=IF([Date of Review]<"10/1/2021","",IF(AND([Date of Review]>="10/1/2021",[Date of Review]<="12/31/2021"),"FY22_Q1",IF(AND([Date of Review]>"12/31/2021",[Date of Review]<="3/31/2022"),"FY22_Q2",IF(AND([Date of Review]>"3/31/2022",[Date of Review]<="6/31/2022"),"FY22_Q3","FY22_Q4"))))

The "Date Of Review" column is formatted as "mm/dd/yyyy", but when I added a value that is greater than "10/1/2021", for example, "2/23/2022", the calculated column always shows blank.

Improve this question

1 Answer 1

Reset to default
0

Please check Date of Review of Date and Time Format with date only option and modify your formula to:

=IF([Date of Review]<Date(2021,10,01),"",
IF(AND([Date of Review]>=Date(2021,10,01),[Date of Review] 
<=Date(2021,12,31)),"FY22_Q1",
IF(AND([Date of Review]>Date(2021,12,31),[Date of Review] 
<=Date(2022,03,31)),"FY22_Q2",
IF(AND([Date of Review]>Date(2022,03,31),[Date of Review] 
<=Date(2022,06,31)),"FY22_Q3","FY22_Q4"))))

Test result on my end: enter image description here

Improve this answer
1
  • Thank you, Sir! I changed the date format and it eventually works.
    – T Zhang
    Jun 22, 2021 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.