Example of using To string action from Datetime package

Build a bot that compares the current date with the expiration dates of products.

Prerequisites

You need sample inventory data with expiration dates. If you do not have any sample data, download this spreadsheet that contains sample inventory data with expiration dates.

In this example, the bot loops through a list of products in a spreadsheet and compares the expiration date of each product with the current date. If the dates match, the bot marks the product as expired.

Procedure

  1. Create a new bot.
    1. On the left panel, click Automation.
    2. Click Create new > Task Bot .
    3. In the Create Task Bot window, enter a name for the bot.
    4. Accept the default folder location: \Bots\
    5. To change the default bot storage location, click Choose and follow the prompts.
    6. Click Create and edit.
  2. Dates in the sample data are specified in yyyy-MM-dd format. To compare each date to the current date, you need to obtain the current date using the System:Date variable and convert it to the yyyy-MM-dd format. To do so, use the Datetime > To string action and theSystem: Date variable.
    1. From the Actions panel, find and add Datetime > To string action to the Bot editor.
    2. In Source date and time variable, click Variable, and enter $System:Date$. You can use the System: Date variable to get the current date.
    3. In Select date time format, click Custom format, and enter yyyy-MM-dd.
    4. In Assign the output to a variable, click (x) and create a string variable named currentDate.
    5. Save the changes.
  3. Open the spreadsheet with the sample inventory data.
    1. From the Actions panel, find and add Excel basic > Open action to the Bot editor.
    2. In File path, click Desktop file and select the spreadsheet with the sample inventory data.
    3. Select Sheet contains a header.
    4. Select Specific sheet name and enter the sheet name. If you are using the sample spreadsheet downloaded from this page, specify inventory as the sheet name.
    5. In Create Excel session, click Local session and enter Default.
    6. Save changes.
  4. Select the second row in the current spreadsheet.
    1. From the Actions panel, find and add Excel basic > Go to cell action to the Bot editor.
    2. In Cell option, click Specific cell and then enter A2 in Cell name.
    3. In Session name, enter Default.
    4. Save changes.
  5. Iterate through the rows in the spreadsheet and get the expiry date for each product.
    1. From the Actions panel, find and add Loop > Loop action to the Bot editor.
    2. In Iterator, choose Excel basic > For each row in worksheet .
    3. In Loop through, select the All rows variable.
    4. In Session name, enter Default.
    5. In Assign the current row to this variable, click Record, and click (x) to create a record variable named ExcelRow.
    6. Save changes.
  6. Compare each expiry date with the current date.
    1. From the Actions panel, find and add String > compare action within the Loop action.
    2. In Source string, enter $currentDate$.
    3. In Compare to string, enter $ExcelRow[2]$.
    4. In Assign the output to variable, click (x) to create a Boolean variable named datesMatched.
    5. Save the changes.
  7. Verify if the current date matches the expiry date of the current item.
    1. From the Actions panel, find and add If > If action with the Loop action.
    2. In Condition, select Boolean > Boolean condition .
    3. In Boolean variable, click True.
    4. In Operator, select Equals to(=).
    5. In Value, click Variable and enter $datesMatched$.
    6. Save the changes.
  8. If the dates match, move the cursor to the end of the current row.
    1. From the Actions panel, find and add Excel basic > Go to cell action within the If action.
    2. In Cell option, click Active cell and then select End of the row.
    3. In Session name, enter Default.
    4. Save changes.
  9. If the dates match, mark the product as expired.
    1. From the Actions panel, find and add Excel basic > Set cell action within the if action.
    2. In Use, click Active cell.
    3. In Value to set, enter Yes.
    4. In Session name, enter Default.
    5. Save the changes.
  10. Select the first cell in the current row.
    1. From the Actions panel, find and add Excel basic > Go to cell action after the If action but within the Loop action.
    2. In Cell option, click Active cell and then select Beginning of the row.
    3. In Session name, enter Default.
    4. Save changes.
  11. Select the next row before the next iteration.
    1. From the Actions panel, find and add Excel basic > Go to cell action within the Loop action.
    2. In Cell option, click Active cell and then select One cell below.
    3. In Session name, enter Default.
    4. Save changes.
  12. Close the current spreadsheet.
    1. From the Actions panel, find and add Excel basic > Close action after the Loop action.
    2. Select Save the changes when closing the file.
    3. In Session name, enter Default.
    4. Save changes.