while fetching data from excel sheet i m getting the date format (dd/mm/yyyy) but requirement is to change it to (mm/dd/yyyy) format. please help me with this.
How to convert date format(mm/dd/yyyy) to (dd/mm/yyyy) and also how to change string into date format
DateTime.ParseExact(yourdatevariable.toString(), “dd/MM/yyyy”, Globalization.CultureInfo.InvariantCulture)
Use this in an assign activity!
and please add system.globalistaion in imports.
Dates.xaml (5.7 KB)
Here is the workflow!
THANK YOU FOR YOUR HELP
Please mark it as solution
I also have the same problem, and it is strange that I use exactly your example: Dates.xaml, but instead of working directly on the string, I read it from excel, convert it to string, trim the hrs, minutes, and seconds, but it comes the following error message:
The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.
I have imported the system.globalisation.
Anyway I use a tedious method, but work: using .subString to get the year, month, and date, then append them together to form a new string.
You say you’re converting it to string - what variable type is it when you grab it from excel? If it’s already a datetime, then you can just do YourDateVariable.ToString(“MM/dd/yyyy”)
Also, if you could upload the excel sheet with the date that you’re having trouble with that’d make it easier for us to help
Very much appreciate and amazed by your prompt response. I attach the file for your reference, and I am quite sure the variable read from excel is converted to string type using .toString, and I can write it by Write Line activity.
Thanks a lot.
DateTime - Test.zip (20.9 KB)
I take your advice, and I first read the excel data as string, Date1, then convert the sting to a DateTime variable, Date2 = CDate(Date1). Then use your advice Date2.ToString(“yyyy/MM/dd”). It works and is more straight forward.
For my case I have to convert all dates in all rows for example row(7) to dd.mm.yyyy. Do I have to assign a new variable?
what if i have a column of date i want to convert from dd/mm/yyyy to yymm?
how to convert date in the format “DD-MMM-YY” OR “DD-MMM-YY” TO DD/MM/YYYY ?
example 26 March 21 to 26/03/2021 or 19 Feb 2021 to 19/02/2021
new_date = DateTime.ParseExact(string_date.ToString,"DD-MMM-YY", Globalization.CultureInfo.InvariantCulture).ToString("DD/MM/YYYY")
its throwing error … what should be the variable type of new_date string and datetime is not working
new_date should be a datetime variable and string_date should be a string variable.
It looks like there is an error in the ParseExact date format though. Take a look here to see all datetime formats: Custom date and time format strings | Microsoft Docs
26 March 21 would be formatted as “dd MMMM yy” (note the capitalization of M but not d or y, it’s important!) and 19 Feb 2021 would be “dd MMM yyyy”