Skip to main content

Notifications

Community site session details

Community site session details

Session Id : NvxGMW12OXnp0TQDkFQINh
Power Automate - Power Automate Desktop
Unanswered

Determining the date of the most recent weekday

Like (0) ShareShare
ReportReport
Posted on 29 Feb 2024 00:42:51 by 20

In a desktop flow, I am creating a flow that identifies the most recent weekday date on or before yesterday.

I think that I can identify the most recent weekday by listing holidays as shown in the attached Excel and determining whether the date before yesterday is included in it, but I am struggling with how to express it as a flow, and I am wondering if anyone can give me some advice. I would be very grateful if anyone could cooperate.

 

  • Deenuji_Loganathan_ Profile Picture
    6,105 Super User 2025 Season 1 on 29 Feb 2024 at 09:33:10
    Re: Determining the date of the most recent weekday

    @K_IKT031595 

    Please follow the suggested flow.

     

    We utilize an Excel sheet to store holiday data. This data is then converted into a list and passed as an input parameter to the .NET script. The script processes the input date and performs necessary calculations to determine the last weekday prior to the entered date.

     

    .Net scripts do the math and it will return the last week day of entered date by excluding week dates holidays list.

    Deenuji_1-1709199258594.png

     

     

    Code:

    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Documents\\Holidays.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
    Display.InputDialog Title: $'''Enter enter the date to check''' Message: $'''Enter enter the date to check in the format of (YYYY/MM/DD)?''' InputType: Display.InputType.SingleLine IsTopMost: False UserInput=> EnteredDate ButtonPressed=> ButtonPressed2
    SET Out_Date TO $'''%''%'''
    Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
    Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: 0 ColumnAsList=> HolidayAsList
    Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.CSharp Script: $'''
    // Step 1: Calculate Yesterday\'s Date
    DateTime yesterday = Convert.ToDateTime(In_Date).AddDays(-1);

    // Step 2: Check if Yesterday is a Weekday
    while (yesterday.DayOfWeek == DayOfWeek.Saturday || yesterday.DayOfWeek == DayOfWeek.Sunday)
    {
    yesterday = yesterday.AddDays(-1); // Move to previous day
    }

    // Step 3: Check if Yesterday is a Holiday
    while (holidays.Contains(yesterday))
    {
    yesterday = yesterday.AddDays(-1); // Move to previous day
    }

    // Step 4: Output the Most Recent Weekday Date on or Before Yesterday

    Out_Date= yesterday.ToString(\"yyyy-MM-dd\");''' @'name:holidays': HolidayAsList @'type:holidays': $'''List''' @'direction:holidays': $'''In''' @'name:In_Date': EnteredDate @'type:In_Date': $'''String''' @'direction:In_Date': $'''In''' @'name:Out_Date': $'''''' @'type:Out_Date': $'''String''' @'direction:Out_Date': $'''Out''' @Out_Date=> Out_Date
    Display.ShowMessageDialog.ShowMessage Title: $'''Last week day''' Message: $'''Last week day is : %Out_Date%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

     

     

  • kinuasa Profile Picture
    781 Most Valuable Professional on 29 Feb 2024 at 07:11:44
    Re: Determining the date of the most recent weekday

    ファイル名やスクリーンショットの一部が日本語だったので日本語で回答させていただきます。
    他の回答者の方が仰っているようにDayOfWeekプロパティで曜日を取得できますので、このプロパティを使って土日の判定、さらに別途祝日判定用のリストやデータテーブルを用意することで祝日も含めて判定できるかと思います。

    祝日判定用のデータテーブルを使った簡単なフローを作成してみましたのでご参考いただければ幸いです。

    (処理の詳細はコメントをご参照ください)

     

    指定した日時から直近の平日を求めるPower Automate for desktopフロー.png

     

    作成したフローは下記サイトにアップしていますので、コードをコピーしてデザイナー画面に貼り付けることで動作確認できます。

     

  • eetuRobo Profile Picture
    3,273 Super User 2025 Season 1 on 29 Feb 2024 at 06:12:45
    Re: Determining the date of the most recent weekday

    On datetime you can you DayOfWeek. So %CurrentDateTime.DayOfWeek% and that gives you weekday like "Thursday".

    Here is logic how you can check is yesterday or day before was weekend or weekday.
    eetuRobo_0-1709186979874.png
    Do you need to have also holidays like Christmas and national holidays? If so then that can vary depending on country. So you might need to use an API for that.

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Power Automate Desktop

#1
eetuRobo Profile Picture

eetuRobo 18 Super User 2025 Season 1

#2
Nived_Nambiar Profile Picture

Nived_Nambiar 10 Super User 2025 Season 1

#3
stampcoin Profile Picture

stampcoin 6

Overall leaderboard
Loading started