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
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
Retype the " marks around Date1
CDate(row("Date1").ToString("dd/MM/yyyy"))
No it is not fixed. Still the same issue and also want to convert to format MM/DD/YYYY format
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 @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
Hi @Boopathi
try this
DateTime.ParseExact(row(“Date1”).ToString, “mdyyyy”,System.Globalization.CultureInfo.InvariantCulture,“MM/dd/yyyy”)
Thanks
Ashwin S
@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
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
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
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
for taking out some complexity i just played with a message box like this:
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