Power Apps: How to Find the Nth day in a Month

Introduction

While working with dates in Power Apps, we may need to come up with expressions that will predict the 2^nd Tuesday in the current Month or the 4^th Friday of the next month. In such situations, we don’t have a straightforward way or function to calculate the date values.

In this article we will see how to find the Nth day in a month

Scenario

We have built a Business Unit Finance Approval App that accepts input from user related to Procurement Invoices which has to go through an approval process. As per organization mandate, all approvals for a month must be completed by the 3^rd Friday of that month. Failing which the process will be carried forward to the next month.

To identify the 3^rd Friday of the month, we will see how to come up with the expression, so that we can add this date as a Note at the bottom of the app

Implementation

We will be using the below expression to get the 3^rd Friday of Every month

      With    (  
                   {      
                     FirstOfMonth:       Date      (      
                       Year(DatePicker.SelectedDate),      
                       Month(DatePicker.SelectedDate),      
                       1      
                     ),      
                     DayOfWeek: 6,      
                     N: 3      
                   },      
                   DateAdd(      
                     FirstOfMonth,      
                     (N * 7) - Weekday(      
                       DateAdd(      
                         FirstOfMonth,      
                         7 - DayOfWeek      
                       )      
                     )      
                   )      
                 )      

Deep Dive into the Expression

Here, the Start Day of the month is identified by picking the Date Picker in the App. For any selected date in the Date Picker, Year(DatePicker.SelectedDate) and Month(DatePicker.SelectedDate) will give us the corresponding Year and Month . Using this we will create the Start Date of the Month.

FirstOfMonth: Date(
       Year(DatePicker.SelectedDate),
       Month(DatePicker.SelectedDate),
       1
     )

DayofWeek is the day number that we want to process. Here we are trying to find the Nth Friday. Week day number starts with Sunday being 1. Hence Friday will have the week day number as 6. N here indicates the 3^rd Friday of the Month that we are trying to identify.

Say for Instance, if we look at the November month, the Fridays, fall on 5^th,12^th,19^th and 26^th. So the 3^rd Friday is on 19^th.

We will use the below formula to calculate this date

DateAdd(
     FirstOfMonth,
     (N * 7) - Weekday(
       DateAdd(
         FirstOfMonth,
         7 - DayOfWeek
       )
     )
   )
)

DateAdd(FirstOfMonth,(N*7)) will give us the Date 1stNovember + 3*7 which comes as 22^nd November. But we need to subtract 3 days to get the right 3^rd Friday which is 19^th Nov. So as to get the week days that we need to subtract ie:3 we will use the expression :

Weekday(
       DateAdd(
         FirstOfMonth,
         7 - DayOfWeek
       )
     )

Weekday(DateAdd(1^st Nov, 7-6)) will give us Weekday(2^nd Nov) . 2Nov is Tuesday which has the week day number 3.

Thus applying this in the complete formula

DateAdd(11/1/2021,(3*7-3)  will give us 19^th Nov as the date which when checked in the calendar verifies it as the 3^rd Friday of the month.

Summary

Thus, we saw how to find out the Nth day in a month using expression in Power Apps which helps in predicting the Nth date in any month.