Date Format

[areeba]

Hi

I have one requirement in which I have few date columns whose format is '2020-12-31' and data type is STRING but I want to parse them into '31-12-2020' format as DATEdatatype . I am using Multi Field Formula for this but not able to write the correct expression. Can you please help me ?

For eg:

Input:

Contract Date     Hiring date

2020-12-31          2020-8-3

I need to have Output:

Contract Date    Hiring Date

31-12-2020         3-8-2020

Also I have one column called Value  which has following data :

Input:

Value

1.1111

0.8889

0.75

0.0278

I need to have output as

Value

1,1111

0,8889

0,75

0,0278

This value has DOUBLE datatype.

Thanks

Areeba

[areeba]

Hi @AkimasaKajitani 

@This will help a lot , just the last thing.. is there also a way not to include 0 before day and month

for eg:  in the above solution Hiring date is appearing as 03-08-2020 but I want to print it as ‘3-8-2020’ only like without 0 before day and month. 
Is it possible?

Thanks

Areeba

[MarqueeCrew]

@areeba ,

The final formatting is possible, even though it doesn't appear to be directly supported by the date specifiers.  Visually, you can explain this with your statement "like without the 0 before day and month".  If you can change that to TrimLeft(day & month), "0") then the formula would be:

TrimLeft(DateTimeFormat([_CurrentField_],"%d/"),"0") +
TrimLeft(DateTimeFormat([_CurrentField_],"%m/%Y"),"0")

This provides you with the final mile of the challenge.

Cheers,

Mark

[areeba]

Hi @AkimasaKajitani 

I changed the data type from Date to string and applied the settings of Multi field formula , it worked 😊 but only thing is inspite of showing right results it is throwing error "Type Mismatch. Number provided where a string is required."

 should I ignore it ?

[areeba]

Hi @MarqueeCrew 

I tried applying this formula but unfortunately it did not work ☹️. Is it  a another way we can remove 0 after applying multi field formula which @AkimasaKajitani  suggested.

Please note my column has string datatype now.

Thanks

Areeba

[AkimasaKajitani · Accepted solution]

Hi @areeba 

Are there null or numeric data?

If the function work well, you can ignore the message.

And I reply for last question.

This is the way of the unnecessary Zero to erase. 

TrimLeft(REGEX_Replace([_CurrentField_], "-0", "-"),"0")

Only Trim function is not meet, but the RegEx function is very helpful.

Off course, you can merge the previous function

TrimLeft(REGEX_Replace(DateTimeFormat(DateTimeParse([_CurrentField_],"%y-%m-%d"),"%d-%m-%Y"), "-0", "-"),"0")

Oh, very complex!

[areeba]

Hi @AkimasaKajitani 

Bingo ! it worked .You Rock 😇

The answer to your question is that yes there are NULL in that field which I am removing them with Data Cleansing tool as it is of String datatype.

Can those NULL be remove with any addition in this formula?

Regards

Areeba

[AkimasaKajitani · Accepted solution]

Hi @areeba 

Yes, Null is ignored at this formula.

Sample is below.

Input:

Output: