Get the start and end date of an ISO week number and all ISO week numbers in a month in PowerApps

For a Power App that I’m building to be able to book hours per month, I wanted to divide the month into ISO week numbers, and for each week number, a user can enter their hours.

To achieve this, I needed to determine which ISO week numbers fall within a month and what the start date is for a selected week number.

Unfortunately, there is no standard function available within Power Apps for this, so I have built it myself.

My built Power App

The solution

To retrieve the start and end dates of a month, I have used the following code:

Set(
    TimesheetStartDate,
    Date(
        Year(dte_TimesheetMonth.SelectedDate),
        Month(dte_TimesheetMonth.SelectedDate),
        1
    )
);
Set(
    TimesheetEndDate,
    Date(
        Year(dte_TimesheetMonth.SelectedDate),
        Month(dte_TimesheetMonth.SelectedDate) + 1,
        1
    ) - 1
);

Now, I am retrieving the start and end dates of the selected month in TimesheetStartDate and TimesheetEndDate.

Next, I will fetch all the ISO week numbers using the following code:

ClearCollect(
    WeeknumbersDate,
    AddColumns(
        ForAll(
   Sequence(47),
       Date(
        Year(dte_TimesheetMonth.SelectedDate),
        Month(dte_TimesheetMonth.SelectedDate) +1,
        10
    ) - Value
)
   
        ,
        "Weeknumber",
        ISOWeekNum(Value) 
    )
);

This code retrieves all the dates and ISO week numbers of the current selected month, as well as a few dates from the following month and the month before the current month. It stores them in the collection WeeknumbersDate. This is necessary because a week number may partially fall in the previous or next month.

Then, I have a gallery where I populate the Items with the following code to fill in all the ISO week numbers that fall within a month:

Distinct(ForAll(
    Sequence(
        Day(TimesheetEndDate)
    ),
    ISOWeekNum( Date(
        Year(TimesheetEndDate),
        Month(TimesheetEndDate),
        Value
    ))
),Value)

In the OnSelect property, I have provided the following code to store the selected week number:

Set(SelectedWeekNumber, ThisItem.Value);

To determine the first and last day within which a week number falls, I use the following code:

Set(
    StartDateWeek,
Last(Filter(WeeknumbersDate,Weeknumber = SelectedWeekNumber)).Value
);

Set(
    EndDateWeek,
    DateAdd(
        StartDateWeek,
        6,
        TimeUnit.Days
    )
);

This code looks in the WeeknumbersDate collection for all dates where SelectedWeekNumber occurs and then retrieves the last one, which represents the start date of the week 😀.

This way, you can retrieve all week numbers and their corresponding dates.

Closing word

I want to thank Matthew Devaney for his inspiration in building this Power App.