Excel Convert Row Value into Date Format

Help
,
1

Hi,

I am having Excel sheet, From the sheet rows having values (1,2,3,4…) like this… I need to convert the value into Date format like mm/dd/yyyy (6/1/2018,6/2/2018 etc…) Can you please help me to break this logic.

Input Image:

inputDateFile
inputDateFile1027×576 14.7 KB

Output Image:

outputDateFile
outputDateFile398×520 9.67 KB

Thanks,
Arunachalam.

1 Like
2

What is the logic that defines which month to write ?

3

Hi @mustafa.altinbasak

month should be June. Can you help me how to convert row value into dd/mm/yyyy format.

Thanks,
Arunachalam.

4

Hello.

I have not tested this but let me just provide you a quick idea on how to do this.

Let’s assume you run through each row in a For each, then you can manipulate the numbers to the desired date string (I would probably use a .Select expression here)

For row in dt1
    Assign row(1) = System.Text.RegularExpressions.Regex.Matches(row(1).ToString, "[0-9]{1,2}").ToArray.Select(Function(dd) dd.ToString+"/07/2018")

I am not 100% confident I did the .Matches correctly. But, essentially, you want to set the string into an array of numbers, then concatenate the month and year to it for each number (which the .Select() will do if you create the Matches array correctly.) —you can also use the Matches activity, although I’m not sure how well it works.

I hope this helps.

Regards.

2 Likes
5

Thanks @ClaytonM.

I will check and let you know. Thanks for your response.

Thanks,
Arunachalam.

6

Instead of Matches, you can also simply use a .Split(). The reason I didn’t mention it is because sometimes that solution requires some additional logic like checking if the delimitter exists.

That method will look like this:

Assign row(1) = If(row(1).ToString.Contains(","), row(1).ToString.Split({","},System.StringSplitOptions.RemoveEmptyEntries), {row(1).ToString}).ToArray.Select(Function(dd) dd.ToString+"/07/2018")
1 Like
7

@ClaytonM

But my Format should be like this mm/dd/yyyy. Can you please share the code for this.

Thanks,
Arunachalam.

8

Sorry, you said dd/mm/yyyy in previous post.
If you want mm/dd/yyyy just adjust the string concatenation.
Like this:

"7/"+dd.ToString+"/2018"

EDIT: Also, you can replace the month and year with variables, like for example:

Month(Now).ToString+"/"+dd.ToString+"/"+Year(Now).ToString

Regards.

1 Like
9

@ClaytonM

Can you please share me “xaml” file for this.

This is my Excel file
LeaveForm.xlsx (9.0 KB)

Thanks,
Arunachalam.

11

Hello @Arunachalam,

Sorry for the delayed answer, workload has been heavy lately.

Could you please try this ?
ExcelTest.xaml (17.5 KB)

Tested with following input :

Book1.xlsx (8.5 KB)

Please note that you will have some variables to set in the XAML, such as excel file name and sheet name.

Also forgive me for the code’s messy look. Doing this kind of stuff with uiPath and not much time is not easy.
Try to give it a cleaning up whenever you can. :slight_smile:

1 Like
12

Hi,

To change cell (Format) type here is an activity called “ChangeCellType”.

Regards
Balamurugan.S