How can I create a CASE Statement BETWEEN Two Dates?

user_19338707 0
Viewed 446

I am looking to do a CASE statement in a calculated field to identify dates as holidays, and have tried:

CASE
  WHEN
    FORMAT_DATETIME("%Y%M%D", date) BETWEEN 20190218 AND 20190219 OR
    FORMAT_DATETIME("%Y%M%D", date) BETWEEN 20220220 AND 20220222
    THEN 'presidents_day'
  ELSE NULL
END

I am having trouble formatting the date correctly to use the BETWEEN statement and I am getting the error:

Invalid formula - Operator "BETWEEN" doesn't support TEXT BETWEEN NUMBER AND NUMBER. Operator "BETWEEN" supports ANY BETWEEN ANY AND ANY.

attempt_case

Data Set (Google Sheets):

date
17 Feb 2019
18 Feb 2019
19 Feb 2019
20 Feb 2019
19 Feb 2022
20 Feb 2022
21 Feb 2022
22 Feb 2022
23 Feb 2022

Expected output:

date Date_CASE
Feb 17, 2019
Feb 18, 2019 presidents_day
Feb 19, 2019 presidents_day
Feb 20, 2019
Feb 19, 2022
Feb 20, 2022 presidents_day
Feb 21, 2022 presidents_day
Feb 22, 2022 presidents_day
Feb 23, 2022

Google Data Studio report

google-data-studio case
1 Answers

Using the BETWEEN operator in conjunction with the DATE function ("Creates a date" in the YYYY, MM, DD format) would create the required CASE statement (where date represents the date field):

CASE
  WHEN 
    date BETWEEN DATE(2019, 02, 18) AND DATE(2019, 02, 19) OR 
    date BETWEEN DATE(2022, 02, 20) AND DATE(2022, 02, 22) 
    THEN "presidents_day"
  ELSE NULL
END

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

gif

user_128925530
Related