How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"

Anil Rattan

How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"

Anil Rattan

=IF(MONTH(ED2) = 1, "JAN ", IF(MONTH(ED2) = 2, "FEB ", IF(MONTH(ED2) = 3, "MAR ", IF(MONTH(ED2) = 4, "APR ", IF(MONTH(ED2) = 5, "MAY ", IF(MONTH(ED2) = 6, "JUN ", IF(MONTH(ED2) = 7, "JUL ", IF(MONTH(ED2) = 8, "AUG ", IF(MONTH(ED2) = 9, "SEP ", IF(MONTH(ED2) = 9, "OCT ", IF(MONTH(ED2) = 11, "NOV ", IF(MONTH(ED2) = 12, "DEC ")))))))))) + YEAR((ED2))))

This one worked

Paul Newcome

To convert to "Q# yyyy" you would use something along the lines of...

="Q" + IF(MONTH([Date Column]@row) >= 10, "4 ", IF(MONTH([Date Column]@row) >=7, "3 ", IF(MONTH([Date Column]@row) >=4, "2 ", "1 "))) + YEAR([Date Column]@row)

To convert to "Mon yyyy", you could either build out a table where the month number is listed in one column and the desired month text is in another and then use an INDEX/MATCH or VLOOKUP along the lines of

=INDEX([Month Text]:[Month Text], MATCH(MONTH([Date Column]@row), [Month Number]:[Month Number], 0))

or you could use a nested IF statement for each month such as

=IF(MONTH([Date Column]@row) = 1, "Jan ", IF(MONTH([Date Column]@row) = 2, "Feb ", ................................................................................................))))))))))) + YEAR([Date Column]@row)

Anil Rattan

=IF(MONTH(ED2) = 1, "JAN ", IF(MONTH(ED2) = 2, "FEB ", IF(MONTH(ED2) = 3, "MAR ", IF(MONTH(ED2) = 4, "APR ", IF(MONTH(ED2) = 5, "MAY ", IF(MONTH(ED2) = 6, "JUN ", IF(MONTH(ED2) = 7, "JUL ", IF(MONTH(ED2) = 8, "AUG ", IF(MONTH(ED2) = 9, "SEP ", IF(MONTH(ED2) = 9, "OCT ", IF(MONTH(ED2) = 11, "NOV ", IF(MONTH(ED2) = 12, "DEC ")))))))))) + YEAR((ED2))))

This one worked