Hello,
I have a excel which has a column of Dates. I’m creating a new data table using build data table activity.
I’m reading the datecolumn in a string variable like strvar = row.item(“Value Date”).ToString
Again I’m converting that string to datetime using following code:
DateTimevar=Datetime.ParseExact(strvar,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
Here I’m getting error as ‘String was not recognised as a valid datetime’.
Could anyone please help me with this
What was the string you tried to convert that threw the error? Your code is expecting it to be in this exact format: dd/MM/yyyy if it is in any other format, your code will throw an error.
My String was exactly in dd/MM/yyyy format it is example (31/07/2020
)
Hi @Sneha2369
Can you provide the Excel so that working on it will be easy
Happy Automation 
Best Regards
Er Pratik Wavhal 
The error is stating that the string was NOT in the format. Is it possible there are whitespaces before or after the digits? What happens if you use a write line for each string during the code before attempting to convert? I have a feeling you will help you troubleshoot and find the error.
DateFormat.xlsx (10.5 KB)
Please find the attachment I have blank cells in middle is it because of that
Hi @Sneha2369
Try the below statement once :-
DateTime.ParseExact(CDate(strvar).ToString("dd/MM/yyyy"),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)
Mark as solution and like it if this helps you 
Happy Automation
Best Regards
Er Pratik Wavhal
When I use write line 07/31/2020 00:00:00 i’m getting this format
Hi @Sneha2369
So may i know that the new Datatable that you are creating that will only contains the rows who have Dates value in Column “Value Date” ??
Happy Automation
Best Regards
Er Pratik Wavhal
Hi @Sneha2369
So in the same statement just add as below :-
DateTime.ParseExact(CDate(strvar).ToString("dd/MM/yyyy"),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")
Or another way is just do like below :-
CDate(strvar).ToString("dd/MM/yyyy")
This both way will eliminate the timing part at the end of the date
Mark as solution and like it if this helps you
Happy Automation
Best Regards
Er Pratik Wavhal
Conversion from string"" to type ‘Date’ is not valid this is the error
I have one date column to which I gave type as System.DateTime
This error means there is an error in your input file. It is trying to convert an empty string into a date.
I would caution against using CDate as it may cause unexpected and uncaught errors if your data types are changing (e.g. switching from dd/MM/yyyy to MM/dd/yyyy, etc)
yes…This is the error what I got
‘Conversion from string"" to type ‘Date’ is not valid this is the error’
I have some empty Cells in the column.Is it because of that?
Hi @Sneha2369
So bcz of the blank spaces in the “Value Date” Column it is giving you that error
May i know do you want the same Data in New Datatable ??
Happy Automation
Best Regards
Er Pratik Wavhal
Final output should be like this
if there is a blank cell it should fill the cell with above value… I have tried for this but it is filling the cell in 07/31/2020 00:00:00 format