Help

Re: IF Statement - Formatting for a number (date) or text

Solved
Jump to Solution
1977 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kelly_Vorrasi1
6 - Interface Innovator
6 - Interface Innovator

Hi All, 

I'm working on a base that needs several fields to be adjustable to display a date or text. Right now the date is being pulled from some formulas but the text is an option should the user need to add a note there instead. I'd really like to keep this all in one field instead of having a date field and a note field since this is duplicated across several different date fields. I can't figure out how to add a step to the formula to first check if something is text vs a number. Any suggestions? 

 

 

1 Solution

Accepted Solutions

Got it, thanks!

This should do what you're looking for I think

 

IF(
  {MANUAL - TVOD},
  IF(
    ISERROR(
      DATETIME_PARSE(
        {MANUAL - TVOD},
        "MM/DD/YYYY"
      )
    ),
    {MANUAL - TVOD},
    DATETIME_FORMAT(
      DATETIME_PARSE(
        {MANUAL - TVOD},
        "MM/DD/YYYY"
      ),
      "MMMM DD, YYYY"
    )
  ),
  {TVOD Formula}
)

 

Screenshot 2023-07-01 at 5.00.48 PM.png

See Solution in Thread

6 Replies 6

Hmm, could you provide screenshots of your current setup and an example workflow please? 

Sure! Here's a screen shot of the fields being used.

A date is pulled in from a linked field (TVOD Formula) but there is also the option to manually input information (Manual - TVOD), either a new date or a note. All dates need to be in friendly format because it's being read by both domestic and international clients. I'd rather not add another field with notes but keep everything in one final field and I'd like the option of reformatting the manually input date so it's less for the team to type out. 

formatting manual.jpg
final formula.jpg

Thanks for the details!  So if I'm understanding you right:
1. If "MANUAL - TVOD" has a value of "1/23/2023", display as "January 23, 2023" in "TVOD Final"
2. If "MANUAL - TVOD" has a value that is not a date, just display the text in "TVOD Final"
3. If "MANUAL - TVOD" is empty, display the value from "TVOD Formula" in "TVOD Final"

Is that correct?

Yes that's all correct! I keep getting stuck on how to have the formula determine if the text is a number/date or just text. 

Got it, thanks!

This should do what you're looking for I think

 

IF(
  {MANUAL - TVOD},
  IF(
    ISERROR(
      DATETIME_PARSE(
        {MANUAL - TVOD},
        "MM/DD/YYYY"
      )
    ),
    {MANUAL - TVOD},
    DATETIME_FORMAT(
      DATETIME_PARSE(
        {MANUAL - TVOD},
        "MM/DD/YYYY"
      ),
      "MMMM DD, YYYY"
    )
  ),
  {TVOD Formula}
)

 

Screenshot 2023-07-01 at 5.00.48 PM.png

Thank you so much! That worked beautifully!