Press ESC to close

Arjan ter HeegdeArjan ter Heegde Welcome to my blog about Microsoft Power Platform and Dynamics 365

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.

Arjan ter Heegde

Arjan ter Heegde

Leave a Reply

Your email address will not be published. Required fields are marked *