How to change different date formats in a column

[anjani_priya]

If there are different month with year in a column how to change them to the constant format.
how to makes changes for the entire column

[Parvathy (PS Parvathy)]

Hi @anjani_priya

Can you share the sample input of the format that you have in Excel so that I can help you with the syntax.

Regards

[Anil_G (Anil Gorthi)]

@anjani_priya

If you need it on excel …then you can use format cells activity and select custom and give any date format you need

Cheers

[anjani_priya]

Book1.xlsx (15.3 KB)
in input,input2,input3 sheets
the months Apr’22 should replace with 30/4/2023
may’22 should replace with 31/5/2023
like wise in three sheets the month should replace with date format (end date of month)

[Parvathy (PS Parvathy)]

@anjani_priya

=> Read Range all three sheets are store it in three different DataTables.
=> Use For Each Row in Data Table to iterate through each row and in assign activity use below syntax

Str_date= DateTime.ParseExact(CurrentRow("month").ToString,"MMM'yy",System.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy")

Str_date is of DataType System.String.
You can use the same syntax for all three sheets by using For Each Row in Data Table for each Data Table.
Hope it helps!!

[anjani_priya]

Book1.xlsx (16.3 KB)

It didnt changed

[lrtetala (Lakshman Reddy)]

Hi @anjani_priya

Try the below code it is working

DateTime.ParseExact(CurrentRow("Month").ToString.Replace("'", ""), "MMMyy", System.Globalization.CultureInfo.CurrentCulture).AddMonths(1).AddDays(-1).ToString("dd/MM/yyyy")

O/P:

Hope this helps!!

[anjani_priya]

If i want for multiple sheets how to do?

[Anil_G (Anil Gorthi)]

@anjani_priya

looks like the cell does not contain a date at all…

you can do this…read the data into datatable and then use the below in ivoke code by sending dt as in/out argument

dt.AsEnumerable.ToList.ForEach(sub(r) r("month") = DateTime.DaysInMonth(DateTime.ParseExact(r("month").ToString, "MMM'yy", CultureInfo.CurrentCulture).Year,DateTime.ParseExact(r("month").ToString, "MMM'yy", CultureInfo.CurrentCulture).Month).ToString + DateTime.ParseExact(r("month").ToString, "MMM'yy", CultureInfo.CurrentCulture).ToString("/MM/yyyy"))

then write the data back to excel

cheers

[lrtetala (Lakshman Reddy)]

@anjani_priya

Try like this

BlankProcess14.zip (105.3 KB)

Book1 (9).xlsx (15.2 KB)

Hope this helps!!

[anjani_priya]

error

[anjani_priya]

what is the code in assign activity?
in your given input file and screenshot doesnot match

[lrtetala (Lakshman Reddy)]

@anjani_priya

Please check on this xaml

How to change different date formats in a column

@anjani_priya Try like this [image] BlankProcess14.zip (105.3 KB) Book1 (9).xlsx (15.2 KB) Hope this helps!!

There are 2 xamls in this file Sequence xaml is your code. Please ignore main xaml

[anjani_priya]

like this,the date format should be there
cell should show apr’23 but the value inside should be date format
how to change like this?

[anjani_priya]

how to do?this method

[anjani_priya]

this is the method i want

[Anil_G (Anil Gorthi)]

@anjani_priya

did you try what we gave…as per screenshot the date is not last date of month…

cheers

[anjani_priya]

@Anil_G

dt.AsEnumerable.ToList.ForEach(sub(r) r(“month”) = DateTime.DaysInMonth(DateTime.ParseExact(r(“month”).ToString, “MMM’yy”, CultureInfo.CurrentCulture).Year,DateTime.ParseExact(r(“month”).ToString, “MMM’yy”, CultureInfo.CurrentCulture).Month).ToString + DateTime.ParseExact(r(“month”).ToString, “MMM’yy”, CultureInfo.CurrentCulture).ToString(“/MM/yyyy”))

[Shiva_Nikhil]

@anjani_priya

you can try this once

Sequence2.xaml (8.2 KB)

[Anil_G (Anil Gorthi)]

@anjani_priya

The datatype shpuld be datatable not string

Cheers