Formatted date is quite confusing

[Kallis]

Hello Friends

I have 2 columns. Both of them are date columns. 

This is the date format

Column 1 - 01.12.2023

Column 2 - 01-12-2023

Column 1 is from SAP whereas Column 2 is what I have formatted

There are more than 100K lines.

It is alright for 90% of the lines but for about 800 lines, the format is different

01.12.2023 is 01.11.2023 

04.12.2023 is 04.11.2023 

so on and so forth

I do not know why this happens. Any help is appreciated

This is how I formatted Column 2

RequestApproveDate (Formatted) = 

Substring([RequestApproveDate]
,6,2)+"."+Substring([RequestApprove Date],4,2)+"."+Substring([RequestApproveDate],0,4)

Some examples

RequestApproveDate	RequestApproveDate (Formatted)
15.09.2023	15.08.2023	False
25.10.2023	25.08.2023	False
08.05.2023	08.04.2023	False
02.08.2023	02.07.2023	False
04.09.2023	04.08.2023	False
06.02.2024	06.01.2024	False
04.05.2023	04.04.2023	False
25.10.2022	25.09.2022	False
10.11.2022	10.10.2022	False
17.02.2023	17.01.2023	False
08.05.2023	08.03.2023	False
10.05.2023	10.04.2023	False
03.05.2023	03.04.2023	False
22.05.2023	22.04.2023	False
13.06.2023	13.05.2023	False
01.09.2023	01.08.2023	False
01.09.2023	01.08.2023	False
02.10.2023	02.09.2023	False
04.03.2024	04.02.2024	False
06.03.2024	06.02.2024	False
01.05.2023	01.04.2023	False
05.05.2023	05.04.2023	False
04.05.2023	04.04.2023	False
01.05.2023	01.04.2023	False
01.05.2023	01.04.2023	False
16.05.2023	16.04.2023	False
03.05.2023	03.04.2023	False

[binuacs]

@Kallis Can you provide a sample [Work Item Created Date] and the expected result?

[Kallis]

Hi @binuacs 

Workitem Created Date - 12.03.2023

Expected Result - 12-03-2023

[binuacs]

@Kallis Alteryx only support YYYY-MM-DD format as date data type, rest all will be considered as string data type

[Kallis]

I thought so too @binuacs 

Converting to Alteryx date format is not a problem

How to convert the date to 10.03.2023 and maintain that as date datatype is the challenge

[Kallis]

@binuacs my requirement is something else

I have 2 columns. Both of them are date columns. 

This is the date format

Column 1 - 01.12.2023

Column 2 - 01-12-2023

Column 1 is from SAP whereas Column 2 is what I have formatted

There are more than 100K lines.

It is alright for 90% of the lines but for about 800 lines, the format is different

01.12.2023 is 01.11.2023 

04.12.2023 is 04.11.2023 

so on and so forth

I do not know why this happens. Any help is appreciated

[binuacs]

@Kallis to answer your question "How to convert the date to 10.03.2023 and maintain that as date datatype is the challenge", as I said before Alteryx only support the ISO format which is YYYY-MM-DD, this is the only Date Type which alteryx can support. Any other format should be in string data type.

I am still not understanding your requirement here. What exactly you are trying to do with the below formula?

RequestApproveDate (Formatted) = 

Substring([RequestApproveDate]
,6,2)+"."+Substring([RequestApprove Date],4,2)+"."+Substring([RequestApproveDate],0,4)