Convert and keep format of a date

Help StudioX
,
1

Hello!

I need to read some dates from excel and put them in another excel but I need to keep the format. The problem is that not always I get a date cause some dates are not set yet, so sometimes I read a date and sometimes I get a -

I try many things but I just can’t make it work as I want… I’ll explain

My columns in the data table of Uipath and Excel need to be datetime type, cause I have to use them on a formula in another cell.

  • If I read the cells as a string, and then use a write range, if excel cells are date format, it always write them as MM/dd/yyyy when I want to keep the format dd/MM/yyyy. But it writes the “-” correct as it is a string.

So I need to manage them as DateTime variables

  • If I make a variable type datetime called X and convert the string variable Yey of the excel (22/01/2022) it with X = DateTime.ParseExact(Yey,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)
    it works fine, the write range activity keeps the format en excel, the problem is that convert every cell is not an option cause I have these values “-”, so when the assign activity tries to convert X=DateTime.ParseExact(“-”,"dd/MM/… It gets an error, and I cant put another value to that variable cause it is a “-” not a date

  • I tried to replace the “-” with another date, so now I can convert all X variables to Datetime but when I try to replace them again with a “-” it puts the wrong format in the others cause its making them a string again

  • i tried to use “format value” activity with generic value so I can "if X <> “-”, then convert value to date, else nothing, but I get an error “cant convert generic value to datetime”

  • I tried “try catch” try parse the generic value to a date, exception “-” … nothing, got an error cant convert generic to datetime

I surrender, I just dont know what to do

How read a cell, if its a date, keep the format dd/MM/yyyy and if its not, leave the “-” as they are?

Thanks for reading me.

Sorry if I wrote something wrong, not my language.

2

@Mark_Bonnet

Did you try as Read Range activity and set the property Read formatting as Default or try other options as well and check if it is working

image
image894×261 13.6 KB

Hope this may help you

Thanks

3

I don’t have that property in my read range activity. :frowning:

I have one called “Preserve format” and I have it with the check.

4

just use try catch
in try just use assign activity for parsing date as you want, whenever you getting “-” that time it will throw error in catch block just over write same data which you taking from cell

5

How do I overwrite with a “-” a datetime column?
What I do to write the cells is an activity after that, an “add row” and {a, b, c, mydatetimevariable ,e}
And as the parse is in a date time variable and my columns are datetime, I cant write the “-”, or can I? I tried but I don’t dont if I did it correctly.

6

in try block
datetimevar= datetime.parse (…)

add data row = {a,b,datetime var}
catch block
var2=“-”
add data row = {a,b,var2}

7

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