Convert Date format to Text Format

[Balachander_Pandian]

Hi

I have an Excel file which contain ALLOTMENTDATE column as it is Date format (04/05/2023) I wish convert this as Text format with a Linq Query to achieve this. Please guide me to resolve this
Input.xlsx (22.5 KB)

[Shiva_Nikhil]

@Balachander_Pandian

input is 08/10/2023 you want output as Sunday October 2023

check the below xaml

dateformat toText.zip (35.5 KB)

cheers

[Yoichi]

Hi,

Can you share expected output?
FormatCells activity may help you. It can changes format of cells to Text or any datetime style.

Regards,

[Anil_G (Anil Gorthi)]

@Balachander_Pandian

1. What is the target format?
2. If needed on excel then you can use format cells activity
3. If needed in datatable that is when you can use linq…or whilr usign that row as wel you can convert

Cheers

[supriya117 (Supriya Allada)]

Hi @Balachander_Pandian

Try this:
DtExcel is input datatable

dtFormatted = (From row In dtExcel.AsEnumerable()
               Let dateValue As DateTime = DateTime.ParseExact(row("ALLOTMENTDATE").ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture)
               Let formattedDate As String = dateValue.ToString("dddd MMMM yyyy")
               Select dtFormatted.Rows.Add(formattedDate)).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @Balachander_Pandian

Assign: transformedDataTable = (From row In inputDataTable.AsEnumerable()
                                Let dateValue = DateTime.ParseExact(row.Field(Of String)("ALLOTMENTDATE"), "MM/dd/yyyy", CultureInfo.InvariantCulture)
                                Select New Object() {row.Field(Of String)("Column1"), row.Field(Of String)("Column2"), dateValue.ToString("MM/dd/yyyy")}).CopyToDataTable()

In this LINQ query:

• inputDataTable represents the input DataTable containing your Excel data.
• Column1 and Column2 are placeholders for your actual column names. Replace them with the correct column names from your Excel file.
• We use DateTime.ParseExact to parse the date from the ALLOTMENTDATE column in the “MM/dd/yyyy” format.
• We then select a new object with the desired columns, including the transformed ALLOTMENTDATE, and use .CopyToDataTable() to create a new DataTable with the results.

Make sure to replace Column1 and Column2 with the actual column names from your Excel file, and adjust the date format string "MM/dd/yyyy" if your date format is different.

Hope it helps!!

[Balachander_Pandian]

@supriya117

(From row In dtExcel.AsEnumerable()
               Let dateValue As DateTime = DateTime.ParseExact(row("ALLOTMENTDATE").ToString(), "MM/dd/yyyy", CultureInfo.InvariantCulture)
               Let formattedDate As String = dateValue.ToString("dddd MMMM yyyy")
               Select dtFormatted.Rows.Add(formattedDate)).CopyToDataTable()

hi- @Yoichi thank you

[Balachander_Pandian]

hi @Parvathy thank you