Changing a Date to School Year

[jeculbNWRESD]

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?

[sam8 · Accepted 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))
)

[jeculbNWRESD]

Thank you so much!