Write range to Excel worksheet converts text string to the date/time format

Help
, ,
1

I have a VERY simple workflow:
I get the last modified date for a file in the Date Time format. I convert it to string with .ToString method.
Then I do ADD Data Row to the Data Table where the relevant column has a String format.
The data table then is saved to the Excel worksheet by a use of Write Range activity.
To my surprise the text format (the String) changes to the custom date/time format in Excel.
I can convert it manually back to text by a use of Excel TEXT function but this ruins
my automation.
Is there a way to have the string like “dd/MM/yyyy hh:mm:ss” in Excel as text but NOT as date/time ?

2 Likes
2

Yeah @MichaelK,

While adding the data, Concate the string like this ="dd/MM/yyyy hh:mm:ss"

Cheers !! :slightly_smiling_face:

3

Yes thats doable But if you excel column has a format i think that was another issue. @MichaelK

cheers :smiley:

Happy learning :smiley:

2 Likes
4

I cannot specify the format of the excel column as it is created automatically by a Write Range activity

1 Like
5

I just use Add row activity like this:
{ID1.ToString, ID2, DtCr.ToString} in the ArrayRow.
DtCr is a problem variable. I don’t understand how can I do concatenate there.

6

Can you try like this!

{ID1.ToString, ID2, "="""+DtCr.ToString+""""}

1 Like
7

It is still the custom format but not text !

image
image923Ă—528 28.6 KB

1 Like
8

Hi,

I suppose the most reliable way is to add Single quote before your datetime string as the following.

"'"+DtCr.ToString

Regards

9

Are you getting the proper format?

10

At least now I can change the column format to Text in Excel but I still don’t understand why Write Range automatically format this column as the Custom Date/Time

1 Like
11

Hi @MichaelK

I know your problem is already resolved.
However, just for my curiosity whether this is indeed the doing of Write Range activity, I tested your scenario. I was able to write in the excel as String without the single quote.
(via just simply ToString or ToString(“dd/MM/yyyy hh:mm:ss”)

Maybe, check your input or the setting of the excel, in case you want to find the root cause. One thing i noticed from the screenshot is you’re trying to convert it to “dd/MM/yyyy hh:mm:ss” but the DtCr output seems to be in the form of “MM/dd/yyy hh:mm:ss”.
Just my two cents. :slight_smile:

2 Likes
12

Hi @marci080

Thank you for this but I don’t understand where I can find the settings of the excel. Are you talking about the Excel application or something in the UI Path studio ?

13

Hi @MichaelK

Honestly, I also don’t know any settings. m(_ _)m

Maybe, you could check/use my test xaml in your environment
to check if we’ll have the same output in the excel.

DateToStringForum178707.xaml (6.7 KB)
DateToString Forum178707.xlsx (7.1 KB)

2 Likes
14

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