Help

Invalid Switch using Today()

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
632 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel_R
5 - Automation Enthusiast
5 - Automation Enthusiast

I'm getting an invalid Switch formula, even though it's super simple.

I have several columns of singular date fields. Each date field is a milestone. I'm writing a simple Switch formula to check if Today() = one of the date fields, then if so say the column's name.

I've simplified this formula to it's most basic to evaluate against itself and I'm still getting an invalid formula. What am I overlooking? 

 

 

SWITCH(
  TODAY(),
    TODAY(),"yes",
    "no"
)

 

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

SWITCH function patterns can only be strings or numbers. Variables and expressions are invalid.

For example, you could use a conditional expression with a date difference (such as 0 for today).

See Solution in Thread

2 Replies 2
Ron_Williams
6 - Interface Innovator
6 - Interface Innovator

It's the double TODAY() that's causing the error. Im not exactly sure your use case but "today" in a formula doesn't work exactly as expected when comparing it to another date field without formatting it. I would suggest:

IF(DATETIME_FORMAT(YOURDATEFIELD,'M/D/YYYY')=DATETIME_FORMAT(TODAY(),'M/D/YYYY'),"YOUR COLUMN NAME").  You could also nest the if statements if you are looking to compare multiple columns and return the column name of the one that is true like this: 
IF(DATETIME_FORMAT(YOURDATEFIELD1,'M/D/YYYY')=DATETIME_FORMAT(TODAY(),'M/D/YYYY'),"YOUR COLUMN NAME",
IF(DATETIME_FORMAT(YOURDATEFIELD2,'M/D/YYYY')=DATETIME_FORMAT(TODAY(),'M/D/YYYY'),"YOUR COLUMN NAME2",IF(DATETIME_FORMAT(YOURDATEFIELD3,'M/D/YYYY')=DATETIME_FORMAT(TODAY(),'M/D/YYYY'),"YOUR COLUMN NAME3"))) etc.
 
Also, keep in mind without changing the time zone in the date field, it will default to GMT even if you aren't showing the time. Depending on the time of day, This may make your formula return true for dates from yesterday, for example. Turning on "use the same time zone for all collaborators" and setting the time zone will fix this. 
Sho
11 - Venus
11 - Venus

SWITCH function patterns can only be strings or numbers. Variables and expressions are invalid.

For example, you could use a conditional expression with a date difference (such as 0 for today).