Formula IFERROR Calculation Error

quez
quez ⚪️
in Beast Mode

I am attempting to convert a text field of 8 digits into formatted date. The majority of the entries are in yyyymmdd order, but more than a few are in yyyyddmm order, so the original date_format formula is erroring on the rogue entries. IFERROR should return the result of the first formula unless it errors, in which case it should return the result of the second formula. This is my construction:

IFERROR(

 DATE_FORMAT(CONCAT(

  SUBSTRING(`Valid To`,1,4),'-',

  SUBSTRING(`Valid To`,5,2),'-',

  SUBSTRING(`Valid To`,7,2),'%Y-%m-%d')),

 DATE_FORMAT(CONCAT(

  SUBSTRING(`Valid To`,1,4),'-',

  SUBSTRING(`Valid To`,7,2),'-',

  SUBSTRING(`Valid To`,5,2),'%Y-%m-%d')))

This is the error message: Calculation Error : Function DATE_FORMAT expected 2 arguments but got 1. The first DATE_FORMAT formula successfully converts yyyymmdd values into legit dates. How do I get the IFERROR part to move to the second DATE_FORMAT formula if it doesn't? Thanks in advance for any help.

Answers

  • DashboardDude
    DashboardDude 🟢

    Hi @quez ,

    Happy to help, but I have a clarifying question. If you see 20221201, how do you know if that’s December 1, 2022 or Jan 12, 2022?

  • quez
    quez ⚪️

    I'm assuming yyyymmdd structure unless the numbers prove otherwise. Any data outage caused by an ambiguous date incorrectly parsed will the be fault of whoever entered it that way. :-) Thanks for any help you can offer.

Categories