Understanding how to manipulate dates within PowerApps is essential for various applications, including scheduling and time tracking. By mastering the process of retrieving all days in a month, developers can enhance their app’s functionality and user experience.

This article provides a straightforward guide to accomplish this task within the PowerApps environment. We’ll explore different methods using PowerApps functions and formulas to generate a list of dates for each day in a specified month.

Table of Contents

1 PowerApps return all days in a month
2 PowerApps business days in a month

PowerApps return all days in a month

In this example we will learn how to use formula in PowerApps to return all days in a specific month. First we need to generates a collection of dates for each day within the selected month and year. First we need to calculate the start and end dates of the selected month and year, then generates a sequence of numbers representing each day within that range, and finally constructs a date value for each day. The resulting collection contains all the dates for the selected month and year.

The following PowerApps formula is used to generate a collection containing all the days of a specified month.


  ForAll(
    With({SelectedMonth: cmbDateNow.Selected.MonthNumber, SelectedYear: txtYear.Value},
        With({startDate: Date(SelectedYear, SelectedMonth, 1), 
            endDate: DateAdd(DateAdd(Date(SelectedYear, SelectedMonth, 1), 1,TimeUnit.Months), -1, TimeUnit.Days)
            },
        ForAll(
                Sequence(DateDiff(startDate, endDate, TimeUnit.Days)+1), 
                Date(SelectedYear, SelectedMonth, Value)
                )
        )
    ),Value)

Let’s break down the code to understand its functionality:

ForAll: This function iterates through a collection or table and evaluates a formula for each record.
With: This function is used to store intermediate results or variables within the formula.
SelectedMonth: It retrieves the month number selected from a combo box named “cmbDateNow”.
SelectedYear: It retrieves the value entered in a text input named “txtYear”.
startDate: It calculates the start date of the selected month and year.
endDate: It calculates the end date of the selected month and year by adding one month to the start date and then subtracting one day.
Sequence: This function generates a sequence of numbers.
DateDiff: It calculates the difference between two dates in terms of a specified time unit (in this case, days).
Date: It constructs a date value using the specified year, month, and day.
Value: It returns the value of the current item in the collection.

So this how PowerApps return all days in a month.

PowerApps business days in a month

Determining business days within a month is a common requirement for tasks such as scheduling, project management, Time sheet ,and resource allocation. Unlike traditional calendars that we generate in the previous example, which consider all days of the month, including weekends and holidays, business days only include weekdays typically observed for work.

We will modify the Previous formula to exclude the week ends ,in this case I consider the week starts with Sunday and week ends to be Friday and Saturday. So we will add condition to check that the weekday of the date is not 6 or 7 , The following formula will return a collection of business days in a month in PowerApps.


  Filter(
    With({SelectedMonth: cmbDateNow.Selected.MonthNumber, SelectedYear.Value},
        With({startDate: Date(SelectedYear, SelectedMonth, 1), 
            endDate: DateAdd(DateAdd(Date(SelectedYear, SelectedMonth, 1), 1, TimeUnit.Months), -1, TimeUnit.Days)
            },
        ForAll(
                Sequence(DateDiff(startDate, endDate, TimeUnit.Days)+1), 
                Date(SelectedYear, SelectedMonth, Value)
                )
        )
    ),
    Weekday(Value) <> 6 && Weekday(Value) <>7
)

Weekday(Value): This function returns the day of the week for a given date, where 1 represents Sunday, 2 represents Monday, and so on.
Filter: This function is used to filter a collection based on a given condition. In this case, it filters the collection of dates to exclude Fridays (Weekday(Value) <> 6) and Saturdays (Weekday(Value) <> 7), ensuring that only weekdays are included. we suppose that the week starts on Sunday, So according to your week ends write the number of the day that you want to exclude.

Overall, this formula generates a collection of dates for weekdays (Sunday to Thursday , The start of my week is Sunday) within the specified month and year, excluding weekends.