Do you need to get a specific day from a date in Power Automate?

Perhaps you need to find one of the following based on a given date,

• Next Monday
• Previous Monday
• First day of the month
• Last day of the month
• First Monday of the month
• Last Monday of the month
• Next business day
• Previous business day
• First business day of the month
• Last business day of the month

Power Automate has some basic date functions, but unfortunately, there isn’t anything that can easily get these calculations based on a given date.

These will need some heavy-duty expressions using the available functions.

Whether you’re looking to retrieve the next Monday from a date, the previous business day, or the first Monday of the month, Power Automate’s functions can get these done with a bit of effort.

In this article, you’ll explore a range of Power Automate functions that enable you to retrieve specific days from input dates.

How to Get the First Day of the Month from a Date

Finding the first day of the month based on a given date will be the only easy task in this post.

This can be easily achieved with the startOfMonth function which will take a date value and return the first day of that month.

@{startOfMonth(
	outputs('Date'),
	'yyyy-MM-dd'
)}

The above expression will return the first day of the month. For example, for the date 2023-07-23, the value 2023-07-01 will be returned.

How to Get the Last Day of the Month from a Date

The startOfMonth function can also be used to get the last day of the month for any given date.

@{addDays(
	startOfMonth(
		addToTime(
			outputs('Date'),
			1,
			'Month'
		)
	),
	-1,
	'yyyy-MM-dd'
)}

The above expression can be used to get the last day of the month from a given date.

The idea behind the calculation is as follows.

1. Add one month to the date using the addToTime function to get the date in the next month.
2. Get the 1st of the month for the date in step 1 with the startOfMonth function.
3. Subtract one day from the date in step 2 with the addDays function.

This will result in the last day of the month.

How to Get the Previous Monday from a Date

@{addDays(
	addDays(outputs('Date'),-7),
	mod(
		sub(
			add(7,outputs('Day')),
			dayOfWeek(outputs('Date'))
		),
		7
	),
	'yyyy-MM-dd'
)}

The above formula will find the previous Monday (or any other day of the week) for a given date.

Here’s how it works.

1. addDays(outputs('Date'),-7): This part of the expression subtracts 7 days from the current date where outputs('Date') is the current date.
2. mod(sub(add(7,outputs('Day')),dayOfWeek(outputs('Date'))),7): This part of the expression does a few things:
   • add(7,outputs('Day')) adds 7 to the day of the week number (where Sunday = 0, Monday = 1, …, Saturday = 6).
   • sub(...,dayOfWeek(outputs('Date'))) subtracts the current day of the week number from the result of the previous step.
   • mod(...,7) takes the result and finds the remainder when divided by 7. This effectively wraps around the result to always be within the range of 0-6 depending on the given date.
3. addDays(..., ..., 'yyyy-MM-dd') adds the result of step 2 to the date from step 1 and formats the result in yyyy-MM-dd format.

This expression calculates the date of the previous Monday relative to a given date. If the given date is already a Monday, it will return the date one week prior.

This also works for any weekday. All you need to do is set outputs('Day') a value from 0 to 6 where Sunday = 0, Monday = 1, …, Saturday = 6.

How to Get the Next Monday from a Date

@{addDays(
	outputs('Date'),
	mod(
		sub(
			add(7,outputs('Day')),
			dayOfWeek(outputs('Date'))
		),
		7
	),
	'yyyy-MM-dd'
)}

The above formula will get the next Monday, or other weekday based on your given date.

This will work in a very similar manner to the previous formula for getting the previous weekday except it looks forward

📝 Note: If the given date is already a Monday, then it will return the same date.

How to Get the First Monday in the Month from a Date

@{addDays(
	startOfMonth(outputs('Date'),'yyyy-MM-dd'),
	mod(
		sub(
			add(7,outputs('Day')),
			dayOfWeek(startOfMonth(outputs('Date'),'yyyy-MM-dd'))
		),
		7
	),
	'yyyy-MM-dd'
)}

The above formula can be used to return the first Monday of the month.

It uses the same logic as the formula for getting the next Monday from a date, except first it will transform the date into the first of the month using the startOfMonth function.

How to Get the Last Monday in the Month from a Date

@{addDays(
	addDays(
		addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd'),
		if(
			equals(
				dayOfWeek(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd')),
				outputs('Day')
			),
			0,
			-7
		)
	),
	mod(
		sub(
			add(7,outputs('Day')),
			dayOfWeek(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd'))
		),
		7
	),
	'yyyy-MM-dd'
)}

The above formula can be used to return the last Monday of the month.

It uses the same logic as the formula for getting the first Monday from a date, except it will transform the date into the last of the month. This is done with the addDays, startOfMonth, and addToTime functions as seen in a previous section of this post.

How to Get the Next Business Day from a Date

@{if(
	equals(dayOfWeek(outputs('Date')),5),
	addDays(outputs('Date'),3,'yyyy-MM-dd'),
	if(
		equals(dayOfWeek(outputs('Date')),6),
		addDays(outputs('Date'),2,'yyyy-MM-dd'),
		addDays(outputs('Date'),1,'yyyy-MM-dd')
	)
)}

The above formula can be used to return the next business day from the date.

This assumes that a business day is Monday through Friday, and doesn’t account for any holidays.

It uses a basic if logic and tests if the date is a Friday or Saturday, and adjusts the next day accordingly to a business day.

How to Get the Previous Business Day from a Date

@{if(
	equals(dayOfWeek(outputs('Date')),1),
	addDays(outputs('Date'),-3,'yyyy-MM-dd'),
	if(
		equals(dayOfWeek(outputs('Date')),0),
		addDays(outputs('Date'),-2,'yyyy-MM-dd'),
		addDays(outputs('Date'),-1,'yyyy-MM-dd')
	)
)}

The above formula can be used to return the previous business day from the date.

It uses the same basic if logic and tests if the date is a Sunday or Monday and adjusts the previous date returned accordingly.

How to Get the First Business Day in the Month from a Date

@{if(
	equals(dayOfWeek(startOfMonth(outputs('Date'),'yyyy-MM-dd')),6),
	addDays(startOfMonth(outputs('Date'),'yyyy-MM-dd'),2,'yyyy-MM-dd'),
	if(
		equals(dayOfWeek(startOfMonth(outputs('Date'),'yyyy-MM-dd')),0),
		addDays(startOfMonth(outputs('Date'),'yyyy-MM-dd'),1,'yyyy-MM-dd'),
		startOfMonth(outputs('Date'),'yyyy-MM-dd')
	)
)}

The above formula can be used to return the first business day of the month from a given date.

This formula converts the date to the first of the month, then finds the next business day if the first of the month is a Saturday or Sunday. Otherwise, the start of the month is returned.

How to Get the Last Business Day in the Month from a Date

@{if(
	equals(dayOfWeek(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd')),6),
	addDays(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd'),-1,'yyyy-MM-dd'),
	if(
		equals(dayOfWeek(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd')),0),
		addDays(addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd'),-2,'yyyy-MM-dd'),
		addDays(startOfMonth(addToTime(outputs('Date'),1, 'Month')),-1, 'yyyy-MM-dd')
	)
)}

The above formula can be used to return the last business day of the month from a given date.

This formula converts the date to the last day of the month, then finds the previous business day if the last day of the month is a Saturday or Sunday. Otherwise, the last day of the month is returned.

Conclusions

Getting a specific day from a date is a complex process in Power Automate because of the limited date functions available.

But it is possible to get the required dates by using various date functions in expressions.

Using a combination of addDays, dayOfWeek, startOfMonth, addToTime, plus some other basic logical and math functions a lot of scenarios can be achieved.

Getting the next Monday, previous Monday, first day of the month, last day of the month, first Monday of the month, last Monday of the month, next business day, previous business day, first business day of the month, or last business day of the month is all possible with expressions.

Have you struggled to get a specific day from a date? How did you solve this? Let me know in the comments!