How to change format of excel dates into particular format for particular columns

Hi Everyone,

I need to change the dateformat of excel columns to a particular format “dd-MMM-yyyy” , how can i do it.

Thanks in advance

Hi!

Try this

  1. Assign

LHS: Date1
RHS: CurrentRow(“ColumnName”).ToString

  1. Assign

LHS: Date1
RHS: Cdate(Date1).ToString(“dd-MMM-yyyy”)

Example:

Regards,
NaNi

Hi @kavya.s16

Use For each row in Data Table

DateTime.ParseExact(Row("ColumnName").ToString,("dd.MM.yyyy"),Globalization.
CultureInfo.InvariantCulture).ToString(“dd-MMM-yyyy””)

dd.MM.yyyy - > Your Excel column format

Have look on the Date Format

Here is the Skeleton

Sequence1.xaml (8.1 KB)

Regards
Gokul

the date format in the column is not constant , what can be done

Can you share the sample excel file with Date format @kavya.s16

Hi @kavya.s16

DateTime.ParseExact(row("ColumnName").tostring,ArrDate,System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("dd-MMM-yyyy")

ArrDate → New String(){“21.02.2022”,“21 02 2022”,“21/02/2022”,“21-02-2022”}

In ArrDate u can defined any format.

Regards
Gokul

but i get dates in the form of d-MMM-yy instead of dd-MMM-yyyy

Change the above expression like this (instead of date use formats)

ArrDate → New String()(“dd.MM.yyyy”,“dd MM yyyy”,“dd/MM/yyyy”,“dd-MM-yyyy”,“d-MMM-yy”)

You can pass as many formats you want as per requirements

Regards
Gokul

Passing it in dateformat array is not a issue ig, in custom the default one it takes is d-MMM-yyy so maybe thats y its appearing in that way

Is there any way to fix it?
@Gokul001

Checkout this thread

It will be helpful for you to convert the whole column into a format you need

Regards
Gokul

1 Like

Hi,

Change your system date format from system settings as per required format.

image

Unfortunately i cant use external packages in my project.