# Date Format conversion issue in excel

**URL:** <https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299>\
**Category:** Activities\
**Tags:** linq-query, invoke-vba, vba, question, excel, activities\
**Created:** [January 4, 2024, 10:18am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299 "2024-01-04T10:18:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Divya\_Salve](https://sea2.discourse-cdn.com/uipath/user_avatar/forum.uipath.com/divya_salve/32/268154_2.png) [@Divya\_Salve](https://forum.uipath.com/u/Divya_Salve)\
**Post date:** [January 4, 2024, 10:18am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/1 "2024-01-04T10:18:50Z")

</div>

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](https://forum.uipath.com/uploads/short-url/dFWoAGNT4F72S2J5JTION0dnMdp.xlsx) (29.4 KB)

---

<div class="post-metadata">

**Author:** ![mkankatala](https://sea2.discourse-cdn.com/uipath/user_avatar/forum.uipath.com/mkankatala/32/484097_2.png) [@mkankatala](https://forum.uipath.com/u/mkankatala)\
**Post date:** [January 4, 2024, 10:24am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/2 "2024-01-04T10:24:13Z")

</div>

Hi @Divya_Salve

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

```auto
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

---

<div class="post-metadata">

**Author:** ![Divya\_Salve](https://sea2.discourse-cdn.com/uipath/user_avatar/forum.uipath.com/divya_salve/32/268154_2.png) [@Divya\_Salve](https://forum.uipath.com/u/Divya_Salve)\
**Post date:** [January 4, 2024, 11:09am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/3 "2024-01-04T11:09:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![mkankatala](https://sea2.discourse-cdn.com/uipath/user_avatar/forum.uipath.com/mkankatala/32/484097_2.png) [@mkankatala](https://forum.uipath.com/u/mkankatala)\
**Post date:** [January 4, 2024, 11:15am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/4 "2024-01-04T11:15:43Z")

</div>

Hi @Divya_Salve

Which column you want to convert to unified DateFormat.

Regards

---

<div class="post-metadata">

**Author:** ![Divya\_Salve](https://sea2.discourse-cdn.com/uipath/user_avatar/forum.uipath.com/divya_salve/32/268154_2.png) [@Divya\_Salve](https://forum.uipath.com/u/Divya_Salve)\
**Post date:** [January 4, 2024, 12:01pm UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/5 "2024-01-04T12:01:05Z")

</div>

Column E,F,I in the attached excel

---

<div class="post-metadata">

**Author:** ![lrtetala](https://avatars.discourse-cdn.com/v4/letter/l/e19b73/32.png) [@lrtetala](https://forum.uipath.com/u/lrtetala)\
**Post date:** [January 6, 2024, 9:13am UTC](https://forum.uipath.com/t/date-format-conversion-issue-in-excel/710299/6 "2024-01-06T09:13:08Z")

</div>

> [@Divya\_Salve](#):
>
> 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.

Hi @Divya_Salve

Can you try the following?

 ![image](https://global.discourse-cdn.com/uipath/original/4X/d/3/1/d31704a0590730a75a72caecc2462306b8e1a285.png)

[BlankProcess16.zip](https://forum.uipath.com/uploads/short-url/3TX6qaYSUUuSc4XWplkfzMGz8Di.zip) (649.5 KB)

**Output:**

 ![image](https://global.discourse-cdn.com/uipath/original/4X/1/1/1/1118de81f9871292eb0857f006d4ce76dd51d6d4.png)

[DateFormatIssue\_Sample.xlsx](https://forum.uipath.com/uploads/short-url/zn5KkU9tsFlT6CE8XdOyaTKxEZ9.xlsx) (35.8 KB)

Cheers!!
