Excel Date format

Help Studio
,
1

Hi
I have a excel with 2 sheets. I have to concatenate 3 columns of each sheet and vlookup the column SRno from sheet 1 to sheet 2.But the date formats are different in both the sheets.So i have to change the date format of both sheets and make it same. Is there any method to do that?
Screenshot 2022-11-25 114752

Screenshot 2022-11-25 114726
Screenshot 2022-11-25 114726910×294 29 KB

2

Hi @Amruta_George1

You can sue this if you want to format on excel

Range can be A:A for full a column

image
image1261×363 14.6 KB

If you are reading it into datatable then

Datetime.ParseExact(“Date”,“Format for the given date(MM.dd.yyyy)”,System.Globalization.CulturalInfo.InvariantCulture)

Then use .ToString(“MM/dd/yyyy”) to get all dates in same format

cheers

3

I have used Datetime.parseExtract but thats not working

4

Hi @Amruta_George1

May I know how you used it

Can you share the workflow or the screenshots?

If you are writing vlook up on excel directly then please use the date format option that I showed in the previous post that will format your excel column as required

cheers

5

I have read the excel and 1st assigned it into a string variable as currentrow(“Date”).tostring and in 2nd assign varibale i have given euqtion as Datetime.parseexact(Date,“M/dd/yyyy”,nothing).tostring(“MM/dd/yyyy”)

6

Hi @Amruta_George1

First print the dates and check in which format they are

and if the dates are like MM/dd/yyyy(firstdate) and MM.dd.yyyy(SecondDate)

then use like this

Datetime.ParseExact(currentrow(“Date”).tostring,“MM/dd/yyyy”,System.Globalization.CulturalInfo.InvariantCulture).Tostring(“MM/dd/yyyy”).Equals(Datetime.ParseExact(currentrow(“Date”).tostring,“MM.dd.yyyy”,System.Globalization.CulturalInfo.InvariantCulture).Tostring(“MM/dd/yyyy”))
This will give true if matched

Mostly from excel you might get hours minutes and seconds as well then include hh:mm:ss as well in the initial format

cheers