In sheets, if I want to change a date to a school year (01/01/2024 is actually 2023-24), I write this formula
=if(MONTH(date reference)>=7,
YEAR(date reference) & "-" & RIGHT(YEAR(date reference)+1, 2),
YEAR(date reference)-1 & "-" & RIGHT(YEAR(date reference), 2))
IF the month is greater than or equal to 7, THEN year - right two digits of year plus 1, ELSE year minus 1 - right two digits of year END
I cannot replicate this in looker studio add field, either with CASE or IF, or IF(CONCAT). Any solutions?
Solved! Go to Solution.
Hey there! I think you're really close. I was able to get this to work with the following formula:
IF(MONTH(date)>=7,
CONCAT(YEAR(date), "-", RIGHT_TEXT((YEAR(date)+1),2)),
CONCAT((YEAR(date)-1), "-", RIGHT_TEXT(YEAR(date),2))
)
Hey there! I think you're really close. I was able to get this to work with the following formula:
IF(MONTH(date)>=7,
CONCAT(YEAR(date), "-", RIGHT_TEXT((YEAR(date)+1),2)),
CONCAT((YEAR(date)-1), "-", RIGHT_TEXT(YEAR(date),2))
)
Thank you so much!