Format Date column 0262020 to 02/06/2020

Hi All,

I have a column name - date and it contains date in the format mmddyyyy like 262020 or 02062020 or it can also be like 2062020 but i need to convert it to properly like 02/06/2020. Can someone help me on this?

Thanks,
Ula

Cdate(row(“Date”).ToString(“dd/MM/yyyy”)

Thanks
Ashwin S

Hi @AshwinS2 AshwinS2

Getting below error. Pls help

Retype the " marks around Date1

CDate(row("Date1").ToString("dd/MM/yyyy"))

1 Like

No it is not fixed. Still the same issue and also want to convert to format MM/DD/YYYY format

@Boopathi

I think the issue is that the date you have ain’t a date format, it’s just numbers… why don’t you use assign to split your string with “/” before you convert it to shape that you want?

Hi @Boopathi

Try this

CDate(row(“Date1”).ToString).ToString(“MM/dd/yyyy”)

Thanks
Ashwin S

1 Like

Hi @AshwinS2

This error occurs now. Date1 column in excel is 262020 (i.e February 6 2020) and it should be converted as 02/06/2020

image

Hi @Boopathi
try this

DateTime.ParseExact(row(“Date1”).ToString, “mdyyyy”,System.Globalization.CultureInfo.InvariantCulture,“MM/dd/yyyy”)

Thanks
Ashwin S

Hi @AshwinS2

Getting below error. Do i need to add any namespace

Thanks,
Ula

@Boopathi
Ensure System.globalization namespace is imported

Yes, added by opening xaml in notepad but still getting the validation error

@Boopathi
Have alook on the the Last Part of Statement and Change IT, thenew Format Statement needs to be into the toString
DateTime.ParseExact(row(“Date1”).ToString, “Mdyyyy”,System.Globalization.CultureInfo.InvariantCulture).toString("MM/dd/yyyy”)

Hi @ppr

Thank you. That validation error got fixed but it is throwing below message

image

string contains the value “262020”

@Boopathi So you can use string manipulation to determine if you have the correct date format:

if string length = 8 then should be correct as will be MMDDYYYY

It will be hard to determine if its a month/day if your month is Jan or Oct or Nov

i.e. 1222020 - could be jan 22 2020 or could be dec 2 2020.

From your original post - 262020 - you know if length = 6 then you only have single digit day and month so can pad with 0.

If it is 2062020 you can work out if its going to be a problem date as i mentioned above, if not you can pad accordingly - i.e. 02062020

Thank you but my input string can be either 262020 or 02062020 or 2062020

@Boopathi

That’s what i told at the beginning! u should transform your data to a date format first, it would make it much eaiser for you :slight_smile:

Hi @mz3bel

Thank you. I am trying that one now and last 4 digits I can identify it is year but struggling to identify first 2 or 3 or 4 whether it is month or date. Do you know any regex logic to split?

Thanks,
Ula

@Boopathi

for taking out some complexity i just played with a message box like this:
grafik

and I can confirm other statements form here: is a date ambigious then an exception will be thrown:
112020,1132020 etc as day and month cannot be differentiated. Even 9302020 fails as it could be understandable, but some left to right parsing seems to be applied.

So a first corrective step could e.g. start from right side to split off the year with a space like 9302020 to 930 2020, however other cases will left.

So lets see the further feedback from the community. A check before post with the messagebox way could prevent some nonworking answers

Hi @ppr

Can there be solution for this scenario, right now it works(as you have said) only if the string is in 8 digits eg. 02072020