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.
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:
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
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”)
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”)
@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:
@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:
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”)
@Palaniyappan,
the date format in the Excel is definitely yyyyMMdd - see screenshot of the input file below:
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:
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.