Entering Dynamic Dates to be Used with Downloading Reports on Legacy Apps via Desktop Recordings

[alexj42]

Hey everyone, 

I am trying to record a desktop flow on my company's legacy app that will help automatically download certain reports we need multiple times a day. 

I have been able to work through a majority of the steps and most of my reports are able to be downloaded automatically. I have one problem however. I have my flow work through a few certain steps and fills out prompts for my specific location, buyer information, dates, etc. However, I have run into the problem where I am asked to enter "Today's Date" or a specific set of dates with some of the reports. I am writing to see if anyone knows how I can have my flow dynamically change the date to today's date or a set of dates, so I do not have to go through everyday and change it to the current day's date. 

Thank you, I understand if this is a relatively confusing question so please ask any clarifying questions, any help is much appreciated! 

[MichaelAnnis]

Here you go:

https://www.linkedin.com/pulse/power-automate-desktop-pad-best-practices-part-1-date-michael-annis?t...

Best of luck!

[Agnius · Accepted solution]

Use the Get current date and time action to get the date for today.

Then use Add to date time to calculate whatever you want. Even though the action is called 'Add', you can also add a negative amount of days or months, effectively subtracting a value and thus calculating a date in the past.

For example, using Add to date time and adding -%CurrentDateTime.Day% days to %CurrentDateTime% (the latter being the output of Get current date and time) would give you the last day of the previous month. A datetime type variable has properties for .Day, .Month and .Year that allow retrieving various kinds of values and making various calculations for whatever you need.

Anything more specific than the above would require that you provide the types of dates to be calculated.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[alexj42]

Thank you! This helps a lot. Another little aspect of this I have a question about.

I am also trying to have one of my flows enter two separate sets of dates in order to yield data from the current time period (ex: 20230701-20230707). Any ideas on how I can get that first date to stay the same for the month, but also move onto the next month when it comes around? 

Thank you for your help

[Agnius]

When you have the %CurrentDateTime% after using the Get current date and time action, you can use that for the current date (end of your period). You can then use Add to date time to add -%CurrentDateTime.Day-1% (minus the current day minus one, meaning if the current day is the 7th, you'd be deducting 6) to get the first day of the current month. That's the start of your period.

If you want them formatted in a specific format, you need to use Convert date time to text to convert them to a sting value that you can then use.

[alexj42]

I figured out how to do this when it regards just the first day of the month. I am now wondering how this would work since these time periods I am needing do not always start on the first, (ex. June's month begins on 5/27, August begins on 7/29, September on 8/26). Do you know how this could work? I have it currently set to input the first of the current month, but would prefer it to go more along with our business calendar. 

Again, thanks for your help 

[Agnius]

Is there some sort of a logic behind these dates? Does it always start on a specific weekday or something? If there is a logical explanation for the algorithm, it can be calculated automatically. If there isn't one, you will need to store a list of dates somewhere and then read that list via PAD and get the date that is applicable to the current period (latest date from the list that is still before the current date).

[alexj42]

We go by a 4-4-5 (each quarter divided into 13 weeks, and each month in each quarter has 4,4, then 5 months) calendar as follows. So every month ends on a Saturday but not always the last Saturday of each month. 

[Agnius]

This would probably be quite tricky to calculate automatically. You could probably do it, but the logic behind the calculation would be quite complex, I think. It's not even that your year starts on the first. So, you would first need to find the first day of the year and then calculate the quarters and the months.

I would suggest storing the list of dates in some file, like an Excel sheet or even a plain text file. Then read it and find the latest date in there that is still before your current date. That would be the easiest to implement.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[alexj42]

What function/s do you think would allow me to extract the most recent date from the excel? Currently just have a separate sub-flow set up to open Excel and recognize the entities in the doc. Thought I'd have to create some sort of output variable but having some trouble doing so. 

[Agnius · Accepted solution]

The following set of actions could do the trick:

Here's a snippet that you can copy and paste directly into PAD to create these actions automatically:

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
SET StartDate TO $'''%''%'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\RPA\\DatesList.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance
Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.CloseExcel.Close Instance: ExcelInstance
Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: $'''Date''' ColumnAsList=> DateList
Variables.SortList.SortList List: DateList
LOOP LoopIndex FROM DateList.Count - 1 TO 0 STEP -1
    IF CurrentDateTime > DateList[LoopIndex] THEN
        SET StartDate TO DateList[LoopIndex]
        EXIT LOOP
    END
END

NOTES

Input file

I just created a very simple Excel file with a single column called 'Date' and the dates there:

That's why I use 'Date' in the Retrieve data table column into list action to get the dates into a list. If your column will be named anything else, you might need to change that in the action.

Retrieving the column

The Retrieve data table column into list action is needed because Read from Excel worksheet will return a table by default, but you need a list to be able to use the Sort list action.

Sorting the list

The Sort list action sorts ascending by default and you cannot change that, unless you enable sorting by item properties. Since I assume the text in Excel to be strings, those do not have valid properties to sort by, so I kept the default and then in the Loop action, I made the flow go backwards from the last item to the first one. This way I start with the latest date and use Exit loop as soon as I find the first date that is lower than the current date.

Date format

The dates in my Excel file are actually formatted in a way that Excel recognizes as date values. This results in PAD also recognizing the values as dates. So, I do not need to convert them to dates and can in fact compare them with the current date right away. However, if your dates are interpreted as strings, you might need to use the Convert text to date time action inside the Loop before using the condition, so that you have two dates to compare, instead of comparing a date to a string (which would result in a type error).

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[alexj42]

Wow this is awesome! I had some of those functions but was really struggling to figure out how to use them for this and how to get them to work. Entered this in as a subflow in mine and it seems to be working as of right now.

Thank you so much, this helped immensely! 

[alexj42]

One more little question for you: 

I have my sample excel sheet of all the dates set up as follows: 

When July 30th comes around, do you think I can leave this excel sheet the same and it will begin to pick the closest date to the current date, so that it will begin taking 7/30 instead of 7/2? Or do you think it will spark some sort of error or type in the right dates? I was thinking if it does end up doing this, then I can just end up trying to design a flow that deletes the top row or date furthest before today's date, and can just move down the line as time goes on. 

I understand if you are not sure what will happen when the date comes around, I appreciate all the help you've given nonetheless. 

[Agnius]

The idea behind the flow itself is that it will loop through the sorted list backwards (from the latest date to the earliest) until it finds a date that is less than your current date, and then stop. So, you do not need to delete any dates. It should select the latest applicable date.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution.

If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/