Hi all, sorry for reviving such an old thread. I wanted to share my experience trying to find a solution for this, as I spent way more time on it than I initially intended to. I hope that one of the methods I list below can save someone else a few headaches.
Just like many other users, my date format in Excel is set to dd/MM/yyyy.
This default formatting is based on the regional settings of your operating system. A quick way to check those is using the command WIN + R and then opening intl.cpl.
Inside my UiPath Studio the default DateTime formatting is set by default to MM/dd/yyyy.
Using System.DateTime.Now would return a DateTime such as “12/09/2022 16:30:21”.
Since this formatting is not what we use here, the commonly used method it to get our local formatting would be System.DateTime.Now.ToString("dd/MM/yyyy") or Date.Today.ToString(“dd/MM/yyyy”).
This does the trick inside the UiPath environment, as the returned String would be “09/12/2022”, but as soon as I used a ‘Write Cell’ activity to return this value to my Excel it would revert the format to MM/dd/yyyy and write “12/09/2022”.
Frustrating, and none of the methods I read in other threads worked for me. I used DateTimeParseExact, substrings, saving it in variables of various types, changed the CultureInfo of the entire project, etc. All to no avail. The result is correct in all my logged messages, but as soon as I write that very same value to Excel, it reverts to the MM/dd/yyyy format.
Using "'" + System.DateTime.Now.ToString("dd/MM/yyyy"), so just adding a ’ before the value to be written into my Excel file was half a solution, but I need to feed this data in a different program that does not accept a ’ before a date value. I wanted to be able to remove this apostrophe in the same run, but doing so would result in the same change in formatting, even when using ‘Modify Text’ on the active Excel value and returning the modified StringValue.
Here’s two methods that did end up working for me:
Instead of trying to convert the DateTime value inside the UiPath environment, I tried to force the ‘Write Cell’ activity to return the value in a specific format.
One method that ended up working was formatting the range of cells in which the DateTime values would be written. Not as text, but as a custom DateTime formatting that forces the locale that I use.
I stumbled upon this bottom option when browsing through the custom formatting options:
This gave me the idea to use a ‘Format Cells’ activity with the following Custom format:
[$-nl-BE]dd/mm/jjjj
Depending on your locale nl-NL, de-DE, en-GB, etc. you should be able to force the DateTime value to be written using the dd/MM/yyyy formatting using the locale of your Excel settings rather than the default setting of the UiPath environment. Note that jjjj is Dutch formatting, it will probably still be yyyy if you use en-GB.
Important: if you do this, there’s no need to format the System DateTime in the UiPath environment for this specific ‘Write Cell’ activity. Just grab the Date part and use that, do not change it to dd/MM/yyyy in Studio. I used DateOnly.FromDateTime(System.DateTime.Now) and stored it in a DateOnly type variable, but I’m assuming Date.Today might work too.
It will show the wrong formatting inside your UiPath environment since you’ll be using the default DateTime format of the environment, but it will get converted in your Excel file.
If I use this variable in my ‘Write Cell’ activity, the returned value in my Excel file uses the dd/MM/yyyy formatting that I need.
Alternative:
If you are unable to pre-emptively format the cell: I found out that even without forcing your locale in the cell formatting, using the DateOnly.FromDateTime(System.DateTime.Now) in my ‘Write Cell’ activity would return the dd/MM/yyyy formatting that I need. I keep using the pre-emptive format so that I know for sure my desired locale will be pushed, but it should also work without it as long as your regional settings are set to a location that uses this type of date formatting. The only notable difference between these two methods is that this alternative would return “9/12/2022” rather than “09/12/2022” since that’s the default Date formatting that gets recognized in my Excel file. If you prefer the double digits, use the Custom formatting method.
It seems that the crux of the issue is that most of us try to convert the DateTime formatting inside the UiPath environment, but that when using the ‘Write cell’ activity some other conversion is done that reverts the desired result back to the original formatting. When refraining from modifying the DateTime value in the UiPath environment, but rather trying to do so in the Excel environment, things worked out for me.
Hopefully this helps someone else too!
