Converting text in MM-dd-yy to date and sort datatable to Export to Excel

[Kyle_Barton1 (Kyle Barton)]

Hello all,

I have a question in regards to reading a text file and converting a field into a date format from it. I have read through multiple forums on converting text to date, which tends to work while inside UiPath. My issue is exporting the data table into Excel using the Write Range Workbook activity. My requirement is to sort the data by date ascending, which I am using a Sort Data Table activity for.

I first am grabbing the date from the text document using a substring. I then am trying to convert that text, which is MM-dd-yy, into a date. Here is where I’m failing. In the new variable I’ve tried using a string, date only and date time variable type. None seem to work. Below is a couple of the assign steps I’ve been trying. Whenever I try to Write Range Workbook activity I get an error that says "String ‘01-01-23’ was not recognized as a valid DateTime(when using date format it says valid Date instead).

If I leave the date as a string I get the format I want but it will not sort in the export to Excel. I do not have Office installed on the VM, so I cannot use the Excel scope to accomplish this. Is there something small I am missing? Any suggestions would be greatly appreciated.

DateTime.ParseExact(DueDate,“MM-dd-yy”,system.Globalization.CultureInfo.InvariantCulture)
DateTime.ParseExact(DueDate,“MM-dd-yyyy”,system.Globalization.CultureInfo.InvariantCulture)
Cdate(Duedate)

[Kaviyarasu_N]

Hi @Kyle_Barton1

It looks like you are trying to convert a text string in the format “MM-dd-yy” to a DateTime object in UiPath, but you are encountering an error when using the “Write Range Workbook” activity.

Code:
DateTime dueDate = DateTime.ParseExact(DueDate, “MM-dd-yy”, System.Globalization.CultureInfo.InvariantCulture)

Thanks!!!

[Anil_G (Anil Gorthi)]

@Kyle_Barton1

I hope you are using workbook activities…first open the exce and check the format…or read the excel using workbook activities and check the format…mostly it would contain a time stamp as well…use the same format instead of what is visible in when you open as excel.then it should be solved

DateTime.ParseExact(DueDate,"MM-dd-yy",system.Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy hh:mm:ss")

Cheers

[Kyle_Barton1 (Kyle Barton)]

Thanks @Kaviyarasu_N. I had my code wrong using “/” instead of “-”. That got me part of the way there. I used @Anil_G suggestion and changed the format of my Excel sheet. It was set to use Date format in Excel. Instead I changed it to General format. Once it exported it send the dates sorted as I programmed. So thank you both.

A side question. Why does the variable have to be datetime and not dateonly for the above to work? If I use DateOnly.ParseExact, or Date.ParseExact, it would through an error saying it could not convert 01-01-23. Just curious on this for my understanding.