Date Format conversion issue in excel

Help Activities
, , , , ,
1

Hi All ,

I have an excel where I need to compare between date columns & update remarks.
These date columns are coming from different sources & are not in proper format.
Some are in dd-mm-yy format , some in dd/MM/yyyy format & few in dd/MM/yyyy hh:mm:ss , we dont know the format in which dates will come.
So using excel formula to compare between these type of columns I’m not able to get proper results.

P.S : I tried with Cdate(cellvalue).tostring(dd/MM/yyyy) and also try excact parse but didnt work everytime and showed string is not valid date time or conversion from string to date is not possible.

Could you please let me know the best way to handle this to work on any kind of format or even to handle null values?

Attaching sample excel for reference.
DateFormatIssue_Sample.xlsx (29.4 KB)

2

Hi @Divya_Salve

You can store the required date formats in an array like below:

str_Array= {"dd-MM-yy","dd/MM/yyyy hh:mm:ss","dd/MM/yyyy"}

you will have to specify the date formats as required.
str_Array is of DataType Array(System.String).

Hope it helps!!
Regards

3

Thanks for the reply.
I wouldnt know which formats I will be getting in the input files.
My goal is to have a unified date format (dd/MM/yyyy) in all date columns so that I can compare easily.

4

Hi @Divya_Salve

Which column you want to convert to unified DateFormat.

Regards

5

Column E,F,I in the attached excel

6

Hi @Divya_Salve

Can you try the following?

image
image900×596 35.6 KB

BlankProcess16.zip (649.5 KB)

Output:

image
image1310×679 17.1 KB

DateFormatIssue_Sample.xlsx (35.8 KB)

Cheers!!