Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Concat field with if/then logic

Topic Labels: Formulas
Solved
Jump to Solution
771 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Mouser-IB-Admin
6 - Interface Innovator
6 - Interface Innovator

Good morning all! I'm hoping someone can help me with turning a pretty straightforward concat formula into something with more if/then logic to help when empty fields are encountered.

Right now I have a primary field on my sheet that uses a formula to build a unique ID for logged payments. That formula is below:

 

 

CONCATENATE({Vendor Name}, " - ", 
IF(
  {Remit to AP},
DATETIME_FORMAT({Remit to AP}, 'MM-DD-YYYY'))
)

 

 

The trouble is that with a lot of records there may not be a "Remit to AP" date for a number of reasons. I'm hoping there's a way I can use alternative fields as backups so the primary field doesn't just show something along the lines of "Vendor - " which isn't helpful.

There's another field called "Invoice Date" that is much more frequently populated, and a "Year" field (which is a single select, not open text) as the last resort. Would it be possible to have a formula that builds the concat as above and says if the "Remit to AP" field isn't blank to use it, but if it's blank then use "Invoice Date" instead, and if that's blank to fall back to "Year" which will never be blank?

Thanks in advance!

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Oh for sure, try:

{Vendor Name} & 
" - " &
IF(
  {Remit to AP},
  DATETIME_FORMAT(
    {Remit to AP}, 
    'MM-DD-YYYY'
  ),
  IF(
    {Invoice Date},
    DATETIME_FORMAT(
      {Invoice Date}, 
      'MM-DD-YYYY'
    ),
    Year
  )
)

Which will give you this:

Screenshot 2024-02-06 at 10.31.12 PM.png

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

Oh for sure, try:

{Vendor Name} & 
" - " &
IF(
  {Remit to AP},
  DATETIME_FORMAT(
    {Remit to AP}, 
    'MM-DD-YYYY'
  ),
  IF(
    {Invoice Date},
    DATETIME_FORMAT(
      {Invoice Date}, 
      'MM-DD-YYYY'
    ),
    Year
  )
)

Which will give you this:

Screenshot 2024-02-06 at 10.31.12 PM.png

That worked perfect, thanks so much! The primary field looks so much cleaner now and should help us in cleaning up older records that had blanks. Have a great day and thanks again for the quick help. 🙂