Concat field with if/then logic

[Mouser-IB-Admin]

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!

[TheTimeSavingCo · Accepted solution]

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:

[Mouser-IB-Admin]

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