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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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