Formula IFERROR Calculation Error

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
-
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?
John Le
You're only one dashboard away.
Click here for more video solutions:
1 -
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.
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 198 Visualize
- 254 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 176 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive