String data manipulation to date

hello i have complex string manipulation to get element from website as part of scrapping and turn it into nice looking date string, the problem i wanna convert it to date so when i export it to excel it would be read as date not string.

here’s my assign and manipulation of the string

flight_date.Remove(0,3).Trim.Replace("th","").Replace("st","").Replace("nd","").Replace("rd","").Replace(" ","-").
Replace("Jan","1").
Replace("Feb","2").
Replace("Mar","3").
Replace("Apr","4").
Replace("May","5").
Replace("Jun","6").
Replace("Jul","7").
Replace("Aug","8").
Replace("Sep","9").
Replace("Oct","10").
Replace("Nov","11").
Replace("Dec","12") +
"-"  + Today.ToString("yyyy")

the date is read as DDD dd^th MMM that’s why i trim it and add the year as string in the end
but even that is not accurate because dated will soon be in 2021 so today method won’t even work

If you need the current year automatically appended, Today should still work in year 2021.
Do you always need it to be a certain year, for example, 2020?

I am sorry, I am not sure I understand what is the question or problem.

Try setting the Data type of the data column in the DataTable to Date. And write that DataTable to Excel. That may be one way to go.

Or, use Write Cell Excel Activity and instead of writing a value, write a formula to it that converts string form of Date to “Date”.

Here is how this might work (Column A is string, B is a formula that takes values from A):

The resulting value in Column B is of type Date:

One more possibility - Record a macro to select the column and format it to date.

Then once you export the data as string to Excel, execute the Macro to automatically do the conversion. One caveat is that the document extension will be .xlsm instead of xlsx.

but how do i convert a string with that format dd-mm-yyyy to an actual date variable in uipath
because so far i have tried every method and it doesn’t work
always give exception invalid date

Try out this manipulation - date.ParseExact(inputVariable,“dd-MM-yyyy”, system.Globalization.CultureInfo.InvariantCulture)

already did that and didn’t work

@Mohammad_Raafat
please tell us the string that you used for parsing or share a screenshot from your used code

1 Like

I don’t understand what you need to parse. Please be clearer with your question.

Could you show us some sample input that you want to parse?

when i get text in a scrapping process the day and date is extracted like highlighted in screen shot.
but then i clear the weekday / trim it / remove th st nd rd behind day. and i switch it to another valid date format as string but it is still a string not a date so i cannot compare it to another date. and i want to add the valid year for the date scrapped. i want to treat is as date

@Mohammad_Raafat
if we got your right then Sat 17th Oct will look after your cleansing like 17-10-2020 right?
so parsing should work as following

ensure following for this:
grafik

1 Like

Might I suggest a slightly more elegant way to do the same manipulation?

DateTime.ParseExact(flightDate.Substring(4).Replace("th","").Replace("st","").Replace("nd","") + " " + Today.ToString("yyyy") ,"dd MMM yyyy", System.Globalization.CultureInfo.InvariantCulture)
1 Like

If we use RegEx.Replace() with @anupamck’s solution , we can cut down the number of Replace() to just one.

DateTime.ParseExact(RegEx.Replace(flightDate.Substring(4), "(?<=\d{1,2})[^\d\s]+", "") + " " + Today.ToString("yyyy") ,"dd MMM yyyy", System.Globalization.CultureInfo.InvariantCulture)

Don’t forget to import the namespace System.Text.RegularExpressions if you want to use RegEx.Replace().

2 Likes

The ParseExact did work, however it gave errors on some various dates
but i solved that by separating the date into variables and parse them as date with correct format

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.