Convert to specific date format (day . month . year)

[EduardoGS_Carva]

​Hello,
Please, I need dates to be in format day(dot)month(dot)(year).

Examples: 23.12.2020, 18.05.2021 etc

But they can come in Excel files with multiple formats, example:

Translation Date	Document Date		Tax Date	Reversal Posting Date
30/09/2020	9/30/2020		14-Sep-20	44125.00

All of them are dates to be converted to day.Month.year.

First one (30/09/20) I can convert using Replace([Original Date], "/", ".")

which returns the desired result (30.09.2020).

Second one (9/30/2020) I can convert using FormatDate([Original Date], "dd.MM.yyyy")

ex: FormatDate("9/30/2020","dd.MM.yyyy"), which returns the desired result (30.09.2020).

Third case ("14-Sep-20") also is converted using FormatDate, the same function as second case.

But how to convert the last case?
In Excel, it is wrongly formatted as Number instead of Date.

The only 2 functions are currently used and work for the first 3 cases, but how can I do to convert last case, please?

Thank you very much!

Regards,

------------------------------
Eduardo
------------------------------

[shashank.kumar280]

Hi,

You can use the Format Cell action of MS Excel VBO to apply the correct format for Reversal Posting Date column and then use the calculation stages as done for other 3 scenarios.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

[michaeloneil · Accepted solution]

Hi Eduardo

You should be able to use the Excel - VBO Format Cell action to directly amend the format type in the cell. In the input parameters set the cell format to "dd.MM.yy" and provide the cell reference you want it applied to. In this scenario I would normally find the starting cell and the last cell I want to format and apply the format to the entire range rather than doing individual ones with calculation steps to keep the process simpler eg. "A2:D2". 

Hope this was helpful 🙂

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

[NicholasZejdlik]

I would recommend using Shashank's and Michael's solution, because it will still work if the formatting in the source document changes, but I wanted to offer two other alternatives in case they are needed.

If you use a code stage, you can use the DateTime.FromOADate() function.

You can also calculate the date using calculation stage with this expression:
MakeDate(31, 12, 1899) + MakeTimeSpan([Date] - 1, 0, 0, 0)
This will not function correctly for dates between 1-Jan-1900 and 29-Feb-1900 due to the leap year bug; you would have to add 1 day for any serial values between 1 and 59 (see this StackOverflow post).

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

[EduardoGS_Carva]

​Thank you, @shashank.kumar280 and @Michael ONeil

Format Cell can be used in Excel BO in BluePrism

​​

Thank you everyone, it works, but the sheet is not recalculated and the new format does not appear until I save, close and reopen the file.
(I need to go to cell that changed, press F2 to edit and press Enter, then it gets re-calculated and shows the new format, dd.MM.yyyy).

Thank you very much, guys!

------------------------------
Eduardo
------------------------------

[NileshJadhavBP]

Hi,

You have to divide 44125.00/86400 and now you have to convert output to date format.(output will come like 0.235638)

I hope it will work you.

Thanks
Nilesh



------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------

[EduardoGS_Carva]

I believe this is the best and fastest approach: select whole range and format it to desired DateFormat in a single action.

Before, I was checking row-by-row if it is DateFormat.
If it is DateFormat in Excel, change to dd.MM.yyyy.
If it is a Text, then replace "/" to ".", as in "30/09/2020" to "30.09.2020".
If it is NumberFormat, then formatting in excel to DateFormat (dd.MM.yyyy) would also work.

Whole range formatting (the best approach) can't be used if some cells are text, because it would turn them into ###########.

Obs: VERY SLOW to check row-by-row as above when 100's rows to check and format.

Thank you very much everyone.
​

------------------------------
Eduardo
------------------------------