Converting date format in an excel cell into another date format

I want to copy a date of a specific excel column for each row and paste it into another column (“Fälligkeit”).
As a next step the date format has to be changed from “yyyyMMdd” to “dd.MM.yyyy”. And this last step is currently not working even though it works if I use hard-coded dates instead of a variable.

So for now my workflow looks like this:

Assign - 1:
Faelligkeit_alt_str = row(0).ToString --> row(0) is where the actual date (“yyyyMMdd”) comes from
Assign - 2:
Faelligkeit_neu_str = DateTime.ParseExact(Faelligkeit_alt_str.ToString(),“yyyyMMdd”,Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

But each time I run this process, I receive following error message:
grafik

The point is, when I now change “Assign - 1” to a hard-coded date with the correct format (e.g. “20181225”) the conversion (to “25.12.2018”) is working.
So I have no clue why it does not work for a variable…
Can someone please help?

Hi @MarcJo,

1.Please check the source from where you are getting is in correct format or not.

2.Sometime we get date as 2018225 in this the month is 2 but it is not in the format you mentioned.You mentioned it as 2 m.

Cheers.
Vashisht.

Hi
use a write line activity and pass the input as row(0).tostring so that we can see what is the format of date been obtained from the excel
if its like dd/MM/yyyy hh:mm:ss
then the expression be like this
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Substring(0,10),“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Cheers @MarcJo

Hi @MarcJo,

I suggest 2 way :

Way : 1

If you are going to copy inside excel file.

  1. Use the copy paste activity Copy Paste Range
  2. You can change the format of cell "Change Cell Type"

Way 2 :

If you are going to copy paste with 2 files.

  1. Use copy activity "Copy ToClipBoard"
  2. Use Paste inside excel activity Paste Clilpboard
  3. You can change the format of cell "Change Cell Type"

Regards
Balamurugan.S

Hi @Vashisht, hi @Palaniyappan
the date format in the Excel is definitely yyyyMMdd - see Output (marked yellow):
image

1 Like

Awesome

So the expression
Be like this
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Substring(0,8).Trim,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Cheers @MarcJo

@Palaniyappan,
after I put
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Substring(0,8).Trim,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)
for “Assign - 2” and ran the process again I received following message:

image

Hmm
Ok remove the Substring alone along the expression and try once
Cheers @MarcJo

@Palaniyappan,
when I take
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Trim,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)
and run again, I receive the same error as before:
image

1 Like

is that yyyyMMdd or yyyyddMM
kindly check that once
based on that
mention the format here in this expression
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Trim,“yyyyMMdd”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”)

Cheers @MarcJo

@Palaniyappan,
the date format in the Excel is definitely yyyyMMdd - see screenshot of the input file below:

image

This Expression
Datetime.ParseExact(Faelligkeit_alt_str.ToString.Trim, “yyyyMMdd” ,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”) is not working.
The Point is when I put a hard-coded date (“20191224”) instead of row(0).ToString I get no error message but the correct Format at the end:

image

Sometimes there are spaces in a date string. Use regex to eliminate space within strings. If you are not familiar with regex functions, the best way is to use the regex builder within the match activitiy.

1 Like

I did it with .Replace(" “,”") at the end of the assign activity instead of Matches (regex options).
But the result was the same…

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