CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Prashanth Cp
Prashanth Cp

Reputation: 57

How to change format of date variable to 'Quarter-Year" in the same cell?

I want to display a date variable and it needs to be shown as Qtr-Year.

E.g. 1/6/19 to be: Q2-19

I can use the roundup(month) formula to make it happen but I need it on the same column as the original date. basically, I want to make changes to the format of the cell.

I tried to use custom formatting formulas but I can't figure out the right one. Also, I have data validation on the column so people can fill in dates through calendar which should automatically get converted to quarter-year.

Upvotes: 0

Views: 2207

Answers (1)

player0
player0

Reputation: 1

this can't be done in the same column. in-cell formatting is way too weak for this task so you need either script or formula:

=ARRAYFORMULA(IF(LEN(Z20:Z), "Q"&IFERROR(VLOOKUP(QUERY({Z20:Z}, 
 "select month(Col1)+1 label month(Col1)+1''"), 
 {1, 1; 4, 2; 7, 3; 10, 4}, 2, 1))&"-"&RIGHT(Z20:Z, 2), ))

0

Upvotes: 1

Related Questions