Change dateformat in row of datatable


#1

Dear reader,

I have a datatable with daterows in the format: 2018-04-01-2018-4-30

I would like to change these daterows to the format: 010418-300418

For me this a difficult one to start with, I work with a ForEach in a datatable activity. Should I use a Regex expression? Does anyone know how this should look like then? What would be the best solution?

Best, Matt


#2

Hi @mattsheep,

Check the source . May a solution for urs.

File : Main.zip (2.3 KB)

Regards
Balamurugan


#3

Hi @balupad14,

Thank you for your help, however it still gives me an error. See the snapshot below.

Other question: do you know where I can put the variable name in the format: strValue.Split(New Char() {"-"c})

Best, Matt

Capture


#4

@mattsheep what you needs to split can you elaborate more with example


#5

Dear @indra,

Thank you for your reply, I will give you a small summary of my first topic.

I have a datatable with daterows in the format: 2018-04-01-2018-4-30

I would like to change these daterows to the format: 010418-300418

For me this a difficult one to start with, I work with a ForEach in a datatable activity. Should I use a Regex expression? What would be the best solution?

Best, Matt


#6

@mattsheep follow below link


#7

The best solution is to save the dates in a DateTime (eg. firstDateVariable & secondDateVariable) variable. Then to output your date in a wanted format simply type firstDateVariable.ToString(“ddMMyy”). dd gives the date in two characters, MM is the month (mm is used for minutes) and yy is the two most righthand characters of the year. (yyyy would result in a 2018 output)


#8

Hi @mattsheep,
In the readCell activity , it has property called result . There you can assign the strValue variable.

Regards
Balamurugan


#9

Dear @balupad14,

For me this is still a difficult one. I tried regex, substring, changed variables, and read a lot on this forum, I still have errors. I attached my workflow to this topic.

The only thing I want is:

From old format: 2018-04-01-2018-4-30

to new format: 010418-300418

What can I do best?

Best, Matt

Change dateformat.xaml (6.0 KB)


#10

Hi @mattsheep,
I have changed your sample that you have attached it here.

File : Change dateformat.xaml (6.3 KB)

you have used the generic variable . I have changed it as string variable.

If you need to use the Generic Variable , use the genVariable.ToString

Regards
Balamurugan


#11

Dear @balupad14,

This amazing, thank you for your help. It makes my day!

Best, Matt


#12

Dear @balupad14,

Now I get the date from a get row activity from a datatable, I think I need to use the generic variable because now the flow doesn’t work anymore. How can I change it to genvariable.ToString?

Additional info: Just changing to GenericValue in the variables pane doesn’t work for me.

Best, Matt


#13

Hi @mattsheep,

Here I have changed the source what you have in the Old one . I handled the generic variable.

File : Change dateformat.zip (1.6 KB)

Regards
Balamurugan


#14

Thank you!! @balupad14


#15

Hi @balupad14,

It seems there are some spaces in the variable I assigned. See the snapshot.

Capture

Do you know how I can delete these, so the format will be “010418-300418”.

Best, Matt


#16

Hi @mattsheep,
I have changed the source again. It is new approach . What ever the format you can able to do with this.

File : Change dateformat1.zip (1.7 KB)

Regards
Balamurugan


#17

Thanks @balupad14, it is perfect.

Best, Matt


#18

hi @mattsheep,

Good


#19

Dear @balupad14 ,

I applied your solution to my Excel with several rows in this old date format. I would like to only use it when the first column is equal to 1. In the output line the answers look perfect!

Now I want to replace the new values over the old values by using add data row in the same Excel. However, I still have errors by using add data row.

Do you have an idea how to replace the new values over the old values? I attached my workflow + Excel in this topic.

Best, Matt

Book1.xlsx (8.5 KB)
Main.xaml (15.7 KB)


#20

Hi @mattsheep,

Check this source.

File :mattsheep.zip (15.0 KB)

Regards
Balamurugan