Date format - uipath - excel

Hello all,

I know there are a lot of date format topics, but I seem not to get this solved properly with the aid of the existing topics.

My issue
In a certain part of my workflow, I have a date stored as a variable var_deadlineconsultant1.
This date is 6/01/2019 (6th of January 2019 = so the format is dd/MM/yyyy).
When using the activity “write cell” to write it in a different excel sheet, the format changes to “1/06/2019” while it should be 06/01/2019 (6th of January 2019), and in my further calcualtions it keeps going with 01/06/2019 (first of june 2019)
How can I solve this?

ADDITION (= edit post):
I am using this function now: convert.ToDateTime(var_deadlineconsultant1).ToString(“dd/MM/yyyy”) and it works when the date is in the format d/MM/yyyy but not when it is already in dd/MM/yyyy. Can I solve this by adding an if function to check what the format of the initial value is? If different from dd/MM/yyyy then convert.ToDateTime(var_deadlineconsultant1).ToString(“dd/MM/yyyy”). But I am struggling how to put that in an if condition.

@ClaytonM, sorry for the tag, but you have been very useful to me in 2018, maybe you can easily help here as well :).

Many thanks in advance,
Kind regards

Hi there @yannip,
This may sound a little odd, but we’ve been able to work around this by prefixing date values with an apostrophe, prior to writing it.

For instance:
'07/01/2019

This also still appears to support formulae.

Quick demo:
Example

Thanks in advance,
Josh

Hi :smiley:

For first issue:
Let me get this right, you are outputting a date as “06/01/2019” basically, and Excel sees it as “June 1”? Well Excel (I believe) goes by the Region and Date format you have set for your computer. You can change this by going to…
Control Panel > Region and Language

After that, Excel should see 06/01 as Jan 6…

Another option would be to send the date in format MM/dd/yyyy and it will go to Excel as Jan 6 as intended, but then you would also need to format that column to the user’s specifications so it displays in dd/MM/yyyy if desired.

(you can also write it as a string with ' infront of the date as was suggested by @Mr_JDavey


For issue 2:
I would recommend that you use DateTime.ParseExact() or DateTime.TryParse() for converting and checking formats, in your case - there are examples of syntax throughout the forums. So, this works better than just converting it as a MM/dd format and just reversing the format, because if the date is in dd/MM initially, then Convert.ToDate() sees it as MM/dd and so it can fail or see the wrong date.

However, to be honest, I’m not sure how to approach a scenario where the date can be in either format, and where you need to figure out which format it is in since 01/06 and 06/01 can both be parsed in any method with no errors. - I am more used to knowing that the format is a certain way during the entirety of a process.

Regards.

1 Like

@Mr_Jdavey, @ClaytonM
Thanks for the suggestion with the apostrophe, but it did not work.

@ClaytonM
I will try with the setting but I am afraid it won’t work :).

In the meantime, as you can see below for the date 07/01/2019, the output frame of the bot shows the correct format, however when writing it to excel it switches to 01/07/2019.

image

@ClaytonM, as expected the error still occurs when changing the format.
Trying the parseexact function gives me this error :(.

You are missing some arguments in the ParseExact()
DateTime.ParseExact(variable,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
or something like that. It should also list the parameters needed when you type the “(” after ParseExact

On your first issue, I’m not sure but I’ll let you know if I come across a solution.

2 Likes

Hi @ClaytonM

Sorry again, but I am missing something now:
Please find the code I used below in a screenshot, and in the next one the output it gives me when writing to excel

image

Slowly getting there I think…

In your Assign, you will probably want to format off the time.
.ToString("dd/MM/yyyy") or .ToString("MM/dd/yyyy")

In the to or in the value property? It is already in the value property but not doing it.

In the value property. Your image shows that it just says .ToString and doesn’t format. So it’s taking that datetime value with the time, which is why it has 0:00 in the cell.

Thanks, however it then again writes 01/07/2019 instead of 07/01/2019.
I guess I’ll just keep it as it is with the hours and minutes, as I can then continue with my calculations.

We can figure this out… Go to Control Panel > Region and Language
What Region do you have set?
For example, “Catalan” has dd/MM/yyyy

If this region is in a different format than what UiPath is using, then this will cause some differences to the formatting in Excel.

The format you provide in the Write Cell needs to match what is in your Region formatting. So if it shows “dd/MM/yyyy” then you need to use .ToString("dd/MM/yyyy"), however if it shows a format of “MM/dd/yyyy” then you need to use .ToString("MM/dd/yyyy")… if you use the wrong one from your region, then it will reverse in Excel.

Thanks @ClaytonM,

The region is set to d/MM/yyyy. However, when i add .tostring("MM/d/yyyy), I get the desired result in the write cell activity.

So, problem solved for now, I just hope further calculations with those dates will not fail :).

Kind regards and thanks for all the insights.

1 Like

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