Invalid Switch using Today()

Question

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"
)

Sho · Accepted Answer

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).

Ron_Williams · Answer

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.

Invalid Switch using Today()

1 Attachments

2 replies

1 Attachments

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded