Date Format "dd/MM/yyyy" Goes Wrong While Writing in Excel

Hello All,

I get a date from Date.Today.ToString(“dd/MM/yyyy”) and my date format in Excel is English(United Kingdom) which is same format (dd/MM/yyyy) . However when I use ‘Write Cell’ function into Excel sheet the format turns to “MM/dd/yyyy”.

How can I solve this problem? (Yes I tried calculate again and text to column function but they don’t fix it neither.)

Also what I realized, it only does this error while there is 01 in the date. So today is 02/01/2020 according to my format bit it keeps writing 01/02/2020 to excel. This error keeps happening to me if there is ‘01’ in the date doesn’t matter in month or day part.

Thanks in advance

@mernst

It’s excel format issue not with your code.

First define required format in that Excel file and then write into Excel file.

I specifically indicated it in my question–> my date format in Excel is English(United Kingdom) which is same format (dd/MM/yyyy)

So yes I did that definition already and it doesn’t fix it.

I think it is worth trying to use " ’ " before and concatenate with your date in the write cell activity, just like: “’” + Date.Today.ToString(“dd/MM/yyyy”)

In visual aspect, it is definitely a smart move but I am uploading this file to SAP later on and SAP doesn’t accept " ’ " .

So it is unfortunately not the solution. Thanks for your suggestion though.

Hi @mernst

try this

Now.ToString(“dd/MM/yyyy”)

Thanks
ashwin S

Hi @mernst,
Just for a reference, can you explain how you defined the date format in the excel?

As in the screen shot.

Are you selecting the entire column while defining the date format?
And also try Custom formatting category and use Type as dd/MM/yyyy

Hi! I have the same issue, what I did was grab the value of the table and convert it to date time, then I write it where I need it with the format I need.

Note: the variable “dateNacimiento” is a DateTime variable.

image

Yes I did for entire column, and nope custom formatting also doesn’t solve the problem.

I also want to repeat again I face with this issue when there is ‘01’ in the date. For the other dates which doesn’t have ‘01’ in it such as "13/08/2019 " this error doesn’t happen. I think it is a bug.

Thank you @dark_vicious but I still look for a better and more practical solution as your solution will make my robot very slow. I have a massive excel sheet and more than one. Writing them one by one is not a case.

Hi @mernst, how did you crack this one ?thx

Hi @mernst, not sure if it is too late. I had the same issue. I resolve it using .ToOADate to convert the date string into double. The double will be converted according to your Excel’s cell format