Start of Month and End of Month in Power Automate Cloud Flows

In this #PowerShot, I will guide you through the process of building logical expressions and utilizing them effectively to determine the start date and the end date of a month based on a specified date in Power Automate Cloud Flows. Power Automate is a powerful tool that allows users to automate workflows and integrate various applications and services seamlessly. Understanding how to manipulate dates is crucial for creating efficient and effective automations.

Understanding Date Functions in Power Automate

Before diving into the specifics of calculating the start and end dates of a month, it is essential to familiarize ourselves with the date functions available in Power Automate. These functions can help you extract various components of a date, such as the year, month, and day, which are pivotal when performing calculations.

Key Date Functions

1. utcNow(): This function retrieves the current date and time in Coordinated Universal Time (UTC).

2. formatDateTime(): This function allows you to format a date into a specific string format, making it easier to display or manipulate.

3. addDays(): This function enables you to add or subtract a specified number of days from a date, which is particularly useful for calculating the end of the month based on the current date.

4. addToTime(): This function enables you to add or subtract from a date time stamp string in units of your choice. You can add or subtract days, months, years, hours, minutes and seconds by providing the time unit in the formula.

Let's Get Started

Step 1: For this example, just to build the logic, I am using a manual date input through the flow trigger. In a different scenario, this date value can be pulled from any of the preceding actions.

Step 2: Calculate the start of month: There are two ways to do this

1. Set the Start of Month Manually: In this case, we can just extract the month and year value from the date value of Step 1 and append '01' to the string so that it marks as the first day of the month. Below is the formula that can be used to get the start of month using this method:

formatDateTime(outputs('Compose_-_Original_Date'),'yyyy-MM-01')

Explanation:

• Here, we are using the formatDateTime function to use the year and month value from the original date and replace the day value with '01'.

• In this formula, outputs('Compose_-_Original_Date') refers to the compose action I set up in the previous step.

2. Calculate Start of Month using a formula: Below is the formula that can be used to get the start of month using this method:

addDays(addDays(outputs('Compose_-_Original_Date'),sub(0,int(formatDateTime(outputs('Compose_-_Original_Date'),'dd')))),1,'yyyy-MM-dd')

Explanation:

• Here, we are first using the sub function to subtract the day value from the provided date from 0. This would return the negative date value. Now as the formatDateTime function returns a string, we are using the int function to convert the day value to an integer.

• Using the addDays function, we are then getting the last day of month from the previous month and then using the other addDays function, we are adding a day to it to get the start of month date of the provided date.

• In this formula, outputs('Compose_-_Original_Date') refers to the compose action I setup in the previous step.

Step 3: Calculate the end of month: In this scenario, it becomes mandatory to use the start of month from the previous step as a month could have 28,29,30 or 31 days depending on the year and the month. Below is the formula that can be used to calculate the end of month:

addDays(addToTime(outputs('Compose_-_Start_of_Month'),1,'Month'),-1,'yyyy-MM-dd')

Explanation:

• At first, we are using the addToTime function to add one month to the start of month date value. Once that is complete, we are using the addDays function to subtract 1 day from the value and then finally format the date in a specific format.

• In this formula, outputs('Compose_-_Start_of_Month') refers to the compose action I set up in the previous step.

Setup Results

Flow run demonstrating date manipulations for February 2024 in a leap year. The original date is set to February 13, 2024, with calculations showing the start and end of the month as February 1 and February 29, respectively.

Flow run demonstrating date processing: Starting with a manually triggered flow, the original date of 2025-05-14 is transformed to the start of the month (2025-05-01) and the end of the month (2025-05-31).

---

By following these steps, you can successfully calculate both the start date and end date of a month based on any given date within Power Automate Cloud Flows. Mastering these date calculations not only enhances your automation capabilities but also allows for more dynamic and responsive workflows. With practice, you will find that manipulating dates becomes an invaluable skill in your Power Automate toolkit.

I hope you found this interesting and it helped you. Thank you for reading!