How to Read Range Date Column from Excel, with a Custom Number Format

[RGT]

I have an excel file where I will read a date in a certain custom format Like this one.

Although it can preserve the slashes for the format, the DataType after reading it will turn it as an object. Doing this, write ranging it after processing the datable would change the Number Format from Custom to General. Is there a way to preserve the Number Format after reading ranging? Thank you.

[jack.chan]

can you manually create an empty excel and set the columns (that you know will be date format) to the custom mm/dd/yyyy format, then save it somewhere

then before you use write range, copy the above excel template to a new file, then use write range to this file. Then your dates will be kept in the custom format

[ushu (Usha kiranmai)]

@RGT Did you try this

While reading range from excel make sure you enable preserve Format

[RGT]

This is actually what my current process right now. Problem is, although it may looks like it uses the same Number Format. Excel doesn’t seem to recognized the dates in the said column, as legit date. Take a look at this one.

Although it was convert to the Correct Date Format, it can’t be converted to a Long Date string.

[RGT]

Yes, I had to do this anyways since read ranging dates without ticking it would just divide or convert the Dates to a number.

[Rahul_Unnikrishnan]

Hello @RGT ,

Can you try converting the dates to your required fomrat. because when you read the excel it will be like a string for each cell. So you can convert that to Datetime format.

How to Convert String to DateTime in UiPath?| Methods of conversion| Practical demo & Tutorial

[jack.chan]

can you show a screenshot of the original excel and the excel after write range?

[RGT]

I did this before. Create a New Data Column set as DateTime, Convert Current values to ShortDateString or Just Date Time, then write range. Although this works well for DateTime, my problem is I need to use the Custom Number Format.

[Rahul_Unnikrishnan]

Did you tried Format Value activity?

[RGT]

Sorry that I can’t send all the datas, but here’s the Column that I’m dealing with.
This is the Input File.

And here’s the output File

Although they may seem to look the same(Both in MM/DD/YYYY format) unlike in the source file, Converting the Number Format of the Output File to other format(Like Long Date String) looks just the same as other formats. This is my indicator that it is indeed, the same Number Format with the Input File.

[jack.chan]

it seems like all your values were converted to string before write range, i tried doing a simple read range and write range to anther cell and it doesnt have this issue,

i manually added a single quote ’ before the date in C2 and it converted to string, otherwise its still a date with valid long date format

did you format your dates after reading range?
and do you mind uploading your xaml here?

[RGT]

I didn’t format it actually, I just left it alone after read ranging. I tried getting the Column Data Type of the Column after read ranging, and it gives me an Object. I’ll try to send a simplified version of the xaml.

[RGT]

Here’s the the xaml file. This is a simple read range and write range workflow.
Read Range.xaml (8.3 KB)
Input.xls (58 KB)
The Input File Contains the BirthDate column, with a Custom Number Format.

[jack.chan]

its because you are using the workbook-read/write range

change to excel application scope and excel - read/write range and it will work

its recommended to always use excel application scope and the excel version of read/write range

[RGT]

Just Tried it, Still the same. I tried converting it to “dd/MM/yyyy” first, to see if the Custom Setting in the Template File would work. Surprisingly, it works if the date’s Month and Date can be interchangble in both date format(Eg, 10/10, 11/12) But Dates like 02/24/2022 and 12/13/2022 doesn’t seem to work.