How to convert to date format from excel data

[Gokul_Murali]

Hello guys i have an excel having a date column i need to get year from the column

This is the code i have

This is the error iam getting

Is there any solution for this

[vrdabberu (Varunraj Dabberu)]

Hi @Gokul_Murali

DateTime.ParseExact(Policydate,"dd-MM-yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy")

Regards

[ashokkarale]

@Gokul_Murali

Use this -

DateTime.ParseExact(row("POLICY FROM").ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy")

Thanks,
Ashok

[Gokul_Murali]

Iam getting an error like this

[vrdabberu (Varunraj Dabberu)]

Hi @Gokul_Murali

Please check if any empty data is being extracted and stored in that variable.

Regards

[Gokul_Murali]

Still showing the error

[vrdabberu (Varunraj Dabberu)]

Can you share the excel? @Gokul_Murali

Regards

[ppr (Peter Preuss)]

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in a hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", CultureInf…

As mentioned there:

the firstAid

• introduces to analysis steps
• demonstrated some fixing options

Kindly note: an empty string cannot be converted into a datetime. In such cases we do:

• defining on how to adress (filter out, forwarding to user interaction for corrections…)
• incorporating a defensive handling when empty strings are found

[Gokul_Murali]

Premium Registers - 11.06.2024.xlsx (11.9 KB)

[vrdabberu (Varunraj Dabberu)]

Hi @Gokul_Murali

Try with the below syntax. I have checked and it’s working fine

CurrentRow("MOC") = DateTime.ParseExact(CurrentRow("POLICY FROM").ToString.Split(" "c).First,"MM/dd/yyyy",System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy")

Regards

[vrdabberu (Varunraj Dabberu)]

Hi @Gokul_Murali

Check the below flow

Sequence9.xaml (9.8 KB)

Regards

[Gokul_Murali]

Bro still iam getting the same error

And the excel i shared is a sample excel the original excel is containing artound 18000 rows

[vrdabberu (Varunraj Dabberu)]

Please check the properties panel of all the activities and please change the seeting in the same way. No matter how many rows the code will work fine !! @Gokul_Murali

regards

[ptrobot]

Your error indicates that one of your Policydate is empty.

You could add a check before converting it with CDate().

PolicyYear = If (String.IsNullOrWhiteSpace(Policydate), "", CDate(Policydate).ToString("yyyy"))

[vrdabberu (Varunraj Dabberu)]

Hi @Gokul_Murali

Please check the below xaml I haved used the invoke code and linq query please check and update me the result:

Sequence9 1.xaml (10.0 KB)

Regards

[AakashVaithiyanthan (Aakash)]

Excel_Date_Conv.xaml (7.6 KB)

Hi refer the attached xaml this may solve the issue.

Solution:
Inside this xaml, Replaced the (-) to (/) in the date for eg: changed the date from 20-06-2024 to 20/06/2024.