Question

Struggling with Dates in bot using Automation Anywhere Community Edition

  • 24 February 2023
  • 18 replies
  • 566 views

Badge +2

Hello,

I am busy building a concept bot, but I am having a problem with one component.

 

Basically, I want the bot to loop through an excel sheet and look at the renewal dates for certificates in a specific column.  If the renewal date is 9 days before the system date, it should give the user a message to say that the certificate is due to expire and needs to be renewed.

 

I've tried searching for a possible solution, but to no avail.

 

Could you perhaps please assist?


18 replies

Userlevel 1
Badge +4

@anjelics. Could do it with Excel formulas after making sure the specific column of dates is formatted as dates. B2 | =A2<=(TODAY()-9)

 

Badge +2

Thank you Steven440, but I was hoping to build a bot that could do it, and not have it be an exercise done in excel.  Just to show a quick concept to my boss about what a bot could do.

Userlevel 7
Badge +13

Hi @anjelics ,

 

You can use the Datetime package for the same and use the Subtract action from it. Select Time value to subtract as 9 and select Days from Time unit to subtract drop-down list. Later on, convert this to a string variable using Date to String action and use that to compare with your excel data.

 

Badge +2

Thank you @Padmakumar.  I have this in one of my steps, but seems like I'm doing something wrong.
And I think the issue lies somewhere in the first step.
 



 

 

 

 

Userlevel 7
Badge +13

Thank you @Padmakumar.  I have this in one of my steps, but seems like I'm doing something wrong.
And I think the issue lies somewhere in the first step.
 



 

 

 

 

 

Are you getting any error while executing this BOT?

Badge +2

Yes, I am.

 

 

Userlevel 7
Badge +13

Yes, I am.

 

 

 

 

You are referencing the Column index in wrong format. 

 

If you are using index to fetch the data, then use $Record[IndexNumber]$ format.

If you are using the column name to fetch the data, then use $Record{ColumnName}$ format.

Badge +2

Thank you.  I took your suggestion.  Now I get this error
 


 

Userlevel 7
Badge +13

Thank you.  I took your suggestion.  Now I get this error
 


 

 

Check what is the format showing for the System variable CurrentDate. Mention the same format in line 4 under Custom format portion. Otherwise, you will get the same error. If you need, later on, you can specify the date format which you actually want while converting the date time variable to String.

Userlevel 5
Badge +9

@anjelics ,

Set in a message box the output from Excel[7].

Let us know the result.

Regards

Badge +2

This is what the message box returns for Excel[7]
 

 

Userlevel 5
Badge +9

@anjelics 

Try to use the Custom Format dd/MM/yyyy when you assign the datatime variable.

 

 

HTH

Regards

Badge +2

Thank you for the suggestion, but I still get the same error :/

Userlevel 7
Badge +13

Thank you for the suggestion, but I still get the same error :/

 

Have you checked date format showing for the System variable Date? You have to specify the same format.

Badge +2

I'm not quite sure how to do that.
I have built a simpler bot in the interim.

 

Userlevel 7
Badge +13

 

I'm not quite sure how to do that.
I have built a simpler bot in the interim.

 

Convert the System variable Date to String and put it in a Message box and see what is the format you are getting there.

Badge +4

For me, the robot works OK¡¡ 🤗

Thanks @Padmakumar

 

Badge

MM/dd/yyyy
dd/MM/yyyy
dd-MM-yyyy

MM-dd-yyyy

I am getting the input date in the above mentioned formats. But I want to convert all the above formats to these format (dd-MMM-yyyy) using Automation Anywhere. I am able to handle one format at a time only, but I want to handle all these above mentioned formats at a time. Is it possible or not? If possible Please suggest me how to do.
Thank you 
Ashok Reddy

Reply