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
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive