String was not recognised as a valid time error in Build Data table

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 :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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 :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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 :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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 :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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)

Hi @Sneha2369

Have you tried what i have given ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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
image

May i know do you want the same Data in New Datatable ??

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

Final output should be like thisFinaloutput

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