Skip to main content
Solved

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

  • June 29, 2023
  • 6 replies
  • 68 views
Kelly_Vorrasi1
Forum|alt.badge.img+9

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? 

 

 

Best answer by TheTimeSavingCo

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

 

6 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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

Adam, Airtable Consultant - thetimesaving.company
Kelly_Vorrasi1
Forum|alt.badge.img+9
  • Kelly_Vorrasi1
  • Author
  • Known Participant
  • June 29, 2023

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. 


TheTimeSavingCo
Forum|alt.badge.img+31

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. 



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?

Adam, Airtable Consultant - thetimesaving.company
Kelly_Vorrasi1
Kelly_Vorrasi1
Forum|alt.badge.img+9
  • Kelly_Vorrasi1
  • Author
  • Known Participant
  • June 30, 2023

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. 

TheTimeSavingCo
Forum|alt.badge.img+31

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

 

Adam, Airtable Consultant - thetimesaving.company
Kelly_Vorrasi1
Forum|alt.badge.img+9
  • Kelly_Vorrasi1
  • Author
  • Known Participant
  • July 3, 2023

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

 


Thank you so much! That worked beautifully! 

TheTimeSavingCo
Terms & ConditionsAccessibility statement