topic RE: How to calculate text dates ? in Product Forum

How to calculate text dates ?

PatrykLiberski — Fri, 19 Jul 2019 10:53:00 GMT

Hello, is it possible to calculate text dates? Or to remove just the rest of my text?

I need to calculate days of leave. I have two data items and I must substract one from another. For example 11/3/2017 12:00:00 AM - 3/31/2017 12:00:00 AM. How can I do this? So far I've tried Replace function. I wanted to use Mid, Trim and Left but the characters in my date items are going to change. so sometimes I will have 9 characters (11/3/2017) and sometimes 8 (1/3/2017).

Which function could I use ?

------------------------------
Patryk
------------------------------

RE: How to calculate text dates ?

david.l.morris — Fri, 19 Jul 2019 10:58:00 GMT

In that particular example, you could use the Utility - Strings object's action 'Split Text' and use "-" as the Split Char. It'll give you a collection with two rows. The first row is your first date and your second row is your second date. You can just loop into the collection or whatever to get your values and use ToDateTime() on them or implicitly cast them into DateTime data items.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: How to calculate text dates ?

PatrykLiberski — Fri, 19 Jul 2019 11:22:00 GMT

but this format is month/day/year and mine is day/month/year

------------------------------
Patryk Liberski
Consultant
Symphony Ventures
------------------------------

RE: How to calculate text dates ?

MichalSzumski — Fri, 19 Jul 2019 11:28:00 GMT

Hi Patryk,

You can convert these data items (ToDate function should work) to date even with time included. Then use function DateDiff to calculate final difference.

Best reards,
Michal

------------------------------
Michal Szumski
RPA developer
Rockwell Automation
Europe/Warsaw
------------------------------

RE: How to calculate text dates ?

PatrykLiberski — Fri, 19 Jul 2019 11:43:00 GMT

I've tried but it failed because of different date formats

------------------------------
Patryk Liberski
Consultant
Symphony Ventures
------------------------------

RE: How to calculate text dates ?

MichalSzumski — Fri, 19 Jul 2019 12:13:00 GMT

HI Patryk,

So let me understand it - You have two text data items with dates but in different format that Your system has - right? and You want to calculate differences between them?

If my understanding is correct then it may be a bit more difficult without specific code stage. What I would do in Your place is:

1. Use below expression to split out date from time for both data items (put data items instead of hardcoded values):
Left("11/3/2017 12:00:00 AM", InStr("11/3/2017 12:00:00 AM", " "))
2. Use Utility - Strings object's action 'Split Text' mentioned before on both data items - using "/" as delimiter
3. Now You know in which row You have days, months and years - You can use "Read Collection Field" from "Utility - Collection Manipulation" to get specific rows to variables to obtain Year, Month and Day. Next You can rearrange Your date in simple calculation.
4. After that You should have two data items with text dates but in correct format - now You can use ToDate and then DateDiff

It is probably over-complicated but this would be my first idea without a code stage for that specific problem - it should work to pass this problem for now.

Best regards
Michal

------------------------------
Michal Szumski
RPA developer
Rockwell Automation
Europe/Warsaw
------------------------------

RE: How to calculate text dates ?

david.l.morris — Fri, 19 Jul 2019 12:30:00 GMT

@PatrykLiberski I would suggest doing it the way Michal explains.

The other way I was just attempting is in C#. I'm only a novice at .NET so it's almost there.

You'd could use a C# code stage, using the namespace import "System.Globalization".

Input to the code stage: dateTimeIn
Output from the code stage: dateTimeOut

C# Code:

var textDateTime = String.Format("{0:dd/MM/yyyy HH:mm:ss}" , dateTimeIn);

dateTimeOut = DateTime.Parse(textDateTime);

This has the same purpose as Michael's solution but doing it a different way. Provide a text string or a DateTime to the input and it'll basically just switch the day and month if what you give it is a string or DateTime where the month and day are already switched to be wrong.

The only issue I have with this is that it's converting to UTC. I'm assuming I'd just need to use SpecifyKind, but you could also just use the Date and Time Manipulation object convert from UTC to Local as well. Anyway, just wanted to provide this as an alternative but I see no reason not to use Michal's suggestion especially since it's more practice with logic in Blue Prism.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: How to calculate text dates ?

PatrykLiberski — Fri, 19 Jul 2019 13:01:00 GMT

@Michal Szumskil thanks, but I cannot read the rest of my date items. I am able to only read the first value so in this case number 11. I cannot read the day nor the month. I have only 1 column in my collection and this "Read Collection Field" from "Utility - Collection Manipulation" reads only the first value. Please have a look on my screenshot

------------------------------
Patryk Liberski
Consultant
Symphony Ventures
------------------------------

RE: How to calculate text dates ?

MichalSzumski — Fri, 19 Jul 2019 13:19:00 GMT

Hi Patryk,

You have to define row index and field name to get specific collection "cell":

0 - means first row, 1 means second row and so on. In Field Name just put name of the column that was created by "Split" action. For me it worked just fine but You need to use this action 3 times for each part of date.

If You really want to avoid using this action from collection manipulation then You could just loop through this collection and count rows. In the same loop You can make choice stage that for row number 1 You will take value as month, for row number 2 You will take day and finally You have year.

It is lot of unnecessary steps but it still should work and You will avoid using action "Read Collection Field" from "Utility - Collection Manipulation".

Best regards
Michal

------------------------------
Michal Szumski
RPA developer
Rockwell Automation
Europe/Warsaw
------------------------------

RE: How to calculate text dates ?

PatrykLiberski — Fri, 19 Jul 2019 13:19:00 GMT

It works now. I've changed the Row Index value. My code is huge now. I will try to modify and improve it. Thank you so much for your help.

------------------------------
Patryk Liberski
Consultant
Symphony Ventures
------------------------------

RE: How to calculate text dates ?

MichalSzumski — Fri, 19 Jul 2019 13:26:00 GMT

Yes - it is over-complicated as I said. I would treat this solution as temporary and for example create later code stage like David suggested to make it more elegant and effective.

Best regards,
Michal

------------------------------
Michal Szumski
RPA developer
Rockwell Automation
Europe/Warsaw
------------------------------