Removing the / or - from a Date format in an Excel

Help Studio
, ,
1

Hi all,

Could you please help me to remove the / or - or . from a date format in Excel Column.

I have a Date column in an excel with different dates.

Ex: 07.02.2023
06.02.2023
04.02.2023
03.02.2023…’

I need to remove “.” from the date and replace the output as
07022023
06022023
04022023
03022023…

Thanks in advance

2

Hi @srinusoft37 ,

Just read the cell value in a string variable and then use replace function to remove “.” with “”

Output_Str = CellValue_Var.Replace(".","")

Regards,
Ashutosh Gupta

3

Hi @srinusoft37

string date = row["Date"].ToString();
string modifiedDate = date.Replace(".","");
row["Date"] = modifiedDate;
4

Hi @srinusoft37

image
image1164×384 12.7 KB
, you can use currentrow for your column.

Regards
Naman Jain

5

@srinusoft37

You can do that by changing the excel column type…

Or

Read the data then loop and use assign inside loop as currentrow("date") = currentrow("date").Tostring.Replace(".","")

Or currentrow("date") = datetime.parseexact(currentrow("date").ToString,"dd.MM.yyyy",System.globalization.cultureinfo.invariantculture).ToString("ddMMyyyy")

Check in log message if the input format of date is same else chnage accordingly dd.MM.yyyy is ehat you need yo change if you see a different format

Cheers

1 Like
6

Thanks for reply,

But we have a huge amount of data, using for each is taking a lot of time.

Can we use any linq expression.

Thanks in advance

7

@srinusoft37

If needed on excel directly use format cell and change the format…

https://www.extendoffice.com/documents/excel/4165-excel-convert-mm-dd-yyyy-to-yyyymmdd.html

Cheers

8

Hi @srinusoft37

Yes you can try below way:

1. Read the excel sheet and store in dt1

2.  use invoke code with dt1 as In/Out arguments with below code 

dt1.AsEnumerable().ToList().ForEach(Sub(row) row("date")= DateTime.ParseExact(row("date").ToString.Trim,"dd.MM.yyyy",System.Globalization.CulturalInfo.InvariantCulture).ToString("ddMMyyyy"))


3. Then u can use write range to write the updated dt1

Check this video if u need to know more about it

thanks & regards,
Nived N

1 Like
9

Thanks @NIVED_NAMBIAR

1 Like
10

You are welcome :slight_smile:

11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.