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

Help Studio
, ,
1

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

1 Like
2

Hi!

Try this

  1. Assign

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

  1. Assign

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

Example:

Date3
Date3833×565 22.4 KB

Regards,
NaNi

3

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

4

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

5

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

6

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.

image
image1300×117 10.6 KB

Regards
Gokul

7

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

8

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

1 Like
9

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

10

Checkout this thread

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

Regards
Gokul

1 Like
11

Hi,

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

image

12

Unfortunately i cant use external packages in my project.

13

There are two ways to change the date format of Excel columns to “dd-MMM-yyyy”:

Method 1: Using the Format Cells dialog box

  1. Select the cells that contain the dates you want to format.
  2. Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open the Format Cells dialog box.
  3. Click the Number tab.
  4. In the Category list, select Custom.
  5. In the Type box, type “dd-MMM-yyyy”.
  6. Click OK.

Method 2: Using the shortcut key

  1. Select the cells that contain the dates you want to format.
  2. Press Ctrl+Shift+# (Windows) or Cmd+Shift+4 (Mac).

If you want to format an entire column to “dd-MMM-yyyy”, you can follow these steps:

  1. Select the entire column.
  2. Right-click on the selected column and choose Format Cells from the context menu.
  3. Follow steps 4-6 from Method 1 above.

I hope this helps!