Invoice Date Format Issue

[SH_94]

Hi Community,

I would like to ask if anyone know how to fix the variable date format as shown below which need multiple formula to fix it.

Many thanks in advance on this.

[atcodedog05]

Hi @SH_94 

This is a common date issue faced. What you can do is write a elseif block to parse each date format like below.

IF !IsNull(DateTimeParse([Initial Date],"%d/%m/%y")) 
// Check whether it falls in the date format
THEN DateTimeParse([Initial Date],"%d/%m/%y") 
// If yes then parse it with a new format
ELSEIF 
!IsNull(DateTimeParse([Initial Date],"%d-%b-%y")) THEN DateTimeParse([Initial Date],"%d-%b-%y")
ELSE Null() ENDIF // Returns null if its a new format

Workflow :

In the similar way if there is more format. You need to write dateparse formats to parse them.

https://help.alteryx.com/current/designer/datetime-functions

Hope this helps 🙂

[atcodedog05]

Hi @SH_94 

Sorry. I just rechecked the red highlighted row. Its a data inconsistency issue that needs to be fixed manually.  Or use a if statement replace that date as a data fix.

[SH_94]

Hi @atcodedog05 ,

Thanks a lot for the workflow provided. 

I got the error as below as i think i forget to mention about the format date : 2021-02-23.

Do you know how can i fix this formula issue?

[SH_94]

Hi @atcodedog05 ,

Thank you for the information provided. 

May i know how we build we build the workflow if the date data consist of inconsistency issue?

Currently i am running about thousand of invoice,do you have any idea on how to build a comprehensive formula to identify if the date whether got the inconsistency issue and how can we fix this if there are issue?

[atcodedog05 · Accepted solution]

Hi @SH_94 

You can fix it by adding the new data format as part of else if block

IF !IsNull(DateTimeParse([Initial Date],"%d/%m/%y")) 
// Check whether it falls in the date format
THEN DateTimeParse([Initial Date],"%d/%m/%y") 
// If yes then parse it with a new format
ELSEIF 
!IsNull(DateTimeParse([Initial Date],"%d-%b-%y")) THEN DateTimeParse([Initial Date],"%d-%b-%y")
ELSEIF 
!IsNull(DateTimeParse([Initial Date],"%Y-%m-%d")) THEN DateTimeParse([Initial Date],"%Y-%m-%d")
ELSE Null() ENDIF // Returns null if its a new format

Hope this helps : )

[atcodedog05]

Hi @SH_94 

I dont know is there an automatic way. One of the ways would be to manually identify the pattern and write a logic for data fix. 

[SH_94]

Hi @atcodedog05 ,

It appear the error again as below. May i know which part i need to amend?

[atcodedog05]

Hi @SH_94 

It's a warning not an error. You can ignore it.

[SH_94]

Hi @atcodedog05 ,

Thanks a lot for the input.