Date of Next Day of Week

takolota · ‎02-17-2022

Often people need to dynamically calculate the date of the next given day of the week.

For example, people may need a set of actions within a flow to be delayed until 5:00PM on Friday every week or any other given time on any given day of the week every week.

The answers to questions like these on forums often only give a solution for the exact day of the week the person is requesting, but if someone needs a different day of the week, then the same formula wouldn't work. This also means the formulas couldn’t be used when the day of the week changes based on other actions.

This attached example flow provides an input & formula that dynamically calculates the date of the next day of the week the user manually inputs or calculates from other flow actions.

It is one set of actions that will work for any requested day of the week, kind of like a DateDayOfWeek expression.

If you don't want to download the flow, then you can just create a Compose action and make sure the action name is just "Compose" with no number count in the name. Then input the number for the day of the week you want in the inputs of that "Compose" action:

1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday

Then make another Compose action, this time the name doesn't matter, and input this expression:

adddays(utcNow(), if(greater(sub(dayofweek(utcNow()), outputs('Compose')),0), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')

Note: If today is the day of the week selected, then it will output today's date. So if it is a Friday, it will show the date for that current Friday, not for the following Friday. You can always adjust this by changing the 0 in the greater than expression to -1.

adddays(utcNow(),
if(greater(sub(dayofweek(utcNow()), outputs('Compose')), -1), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')

And if you need to change the date format, then change the 'yyyy-MM-dd' at the end of the expression. However, this expression will maintain the current time, so if the actions are triggered 10 AM on a Monday and the selected day of week is Friday, then it will output 10 AM Friday. For most scenarios if you need a set time, you can just use a non-time date format like yyyy-MM-dd, then concatenate the static time value to the end of this date with concat(DateOutput, 'T', 'ChosenStaticTimeOn24HrClock', 'Z').

Also be mindful that all this output is based on UTC time, so you may need to adjust your time-zones accordingly.

Code to Copy & Paste Into The "My clipboard" Section of a New Action Menu

{"id":"8d561b06-fd8a-4860-993f-ae66ee891623","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Get_date_of_next_chosen_day_of_the_week","operationDefinition":{"type":"Scope","actions":{"Compose_Choose_the_desired_day_of_week_number":{"type":"Compose","inputs":2,"runAfter":{},"description":"1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday","metadata":{"operationMetadataId":"a09413a3-9fd7-4fd8-815e-0678a12da59a"}},"Compose_Next_date_of_chosen_day_of_week":{"type":"Compose","inputs":"@adddays(utcNow(), \r\nif(greater(sub(dayofweek(utcNow()), outputs('Compose_Choose_the_desired_day_of_week_number')),outputs('Compose_Activity_on_chosen_day_of_week')), \r\nadd(7, sub(outputs('Compose_Choose_the_desired_day_of_week_number'), dayofweek(utcNow()))), \r\nint(replace(string(sub(dayofweek(utcNow()), outputs('Compose_Choose_the_desired_day_of_week_number'))), '-', ''))), \r\n'yyyy-MM-dd')","runAfter":{"Compose_Activity_on_chosen_day_of_week":["Succeeded"]},"description":"adddays(utcNow(), if(greater(sub(dayofweek(utcNow()), outputs('Compose')),0), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')","metadata":{"operationMetadataId":"5ac576b8-c9d3-42c1-bf38-f877ea2590ed"}},"Compose_Activity_on_chosen_day_of_week":{"type":"Compose","inputs":0,"runAfter":{"Compose_Choose_the_desired_day_of_week_number":["Succeeded"]},"description":"If the current day is the chosen day of the week, should it output the current date or the same weekday's date 1 week from now? Enter 0 to output the current day's date on the chosen weekday and enter -1 to output the weekday's date 1 week later.","metadata":{"operationMetadataId":"bb242943-793c-4791-9137-1f991ddb14ad"}}},"runAfter":{},"metadata":{"operationMetadataId":"bd9d9703-1abe-4b07-a4f1-b68604ff84d0"}}}

This set-up was demonstrated in this Power Automate Dates video: https://youtu.be/ybPWtu1i1j0

takolota · ‎02-27-2022

If the formula in the original post looks too confusing, then this one may be easier to understand:

Alternative Formula

if(less(dayofweek(utcNow()), outputs('Compose')),
adddays(utcNow(), sub(outputs('Compose'), dayofweek(utcNow()))),
adddays(adddays(utcNow(), sub(outputs('Compose'), dayofweek(utcNow()))), 7)))

takolota · ‎07-09-2022

I've added a template version that includes a set-up for a Delay until action.

You can input your desired Delay until time & time-zone in the additional Compose actions and the template expressions will format everything for you in the Delay until action.

{"id":"65aa77f8-2d84-4711-90b9-6242c49ce9e2","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Date_next_day_of_week_Delay_until","operationDefinition":{"type":"Scope","actions":{"Scope_Get_date_of_next_chosen_day_of_the_week":{"type":"Scope","actions":{"Compose_Choose_the_desired_day_of_week_number":{"type":"Compose","inputs":6,"runAfter":{},"description":"1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday","metadata":{"operationMetadataId":"a09413a3-9fd7-4fd8-815e-0678a12da59a"}},"Compose_Next_date_of_chosen_day_of_week":{"type":"Compose","inputs":"@adddays(utcNow(), \r\nif(greater(sub(dayofweek(utcNow()), outputs('Compose_Choose_the_desired_day_of_week_number')),outputs('Compose_Activity_on_chosen_day_of_week')), \r\nadd(7, sub(outputs('Compose_Choose_the_desired_day_of_week_number'), dayofweek(utcNow()))), \r\nint(replace(string(sub(dayofweek(utcNow()), outputs('Compose_Choose_the_desired_day_of_week_number'))), '-', ''))), \r\n'yyyy-MM-dd')","runAfter":{"Compose_Activity_on_chosen_day_of_week":["Succeeded"]},"description":"adddays(utcNow(), if(greater(sub(dayofweek(utcNow()), outputs('Compose')),0), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')","metadata":{"operationMetadataId":"5ac576b8-c9d3-42c1-bf38-f877ea2590ed"}},"Compose_Activity_on_chosen_day_of_week":{"type":"Compose","inputs":0,"runAfter":{"Compose_Choose_the_desired_day_of_week_number":["Succeeded"]},"description":"If the current day is the chosen day of the week, should it output the current date or the same weekday's date 1 week from now? Enter 0 to output the current day's date on the chosen weekday and enter -1 to output the weekday's date 1 week later.","metadata":{"operationMetadataId":"bb242943-793c-4791-9137-1f991ddb14ad"}}},"runAfter":{},"metadata":{"operationMetadataId":"bd9d9703-1abe-4b07-a4f1-b68604ff84d0"}},"Compose_Delay_time_of_day":{"type":"Compose","inputs":"19:40","runAfter":{"Scope_Get_date_of_next_chosen_day_of_the_week":["Succeeded"]},"description":"What hour & minutes do you want to Delay until, in your timezone, on a 24-hour clock?","metadata":{"operationMetadataId":"150843f2-cb78-42ab-8cd1-8b74a3f4b449"}},"Compose_Convert_to_UTC_timezone":{"type":"Compose","inputs":"@convertToUtc(outputs('Compose_Delay_time_of_day'), 'Eastern Standard Time', 'HH:mm')","runAfter":{"Compose_Delay_time_of_day":["Succeeded"]},"description":"Delay until actions require UTC timezone inputs. Change 'Eastern Standard Time' to your timezone in the expression below. List of timezone tags: https://docs.microsoft.com/en-us/previous-versions/windows/embedded/ms912391(v=winembedded.11)","metadata":{"operationMetadataId":"5db08ec3-ab29-4fc0-a69d-c8b6164ea963"}},"Delay_until":{"type":"Wait","inputs":{"until":{"timestamp":"@{concat(outputs('Compose_Next_date_of_chosen_day_of_week'), 'T', outputs('Compose_Convert_to_UTC_timezone'), 'Z') }"}},"runAfter":{"Compose_Convert_to_UTC_timezone":["Succeeded"]},"description":"Concatenate the calculated date with the chosen time & the proper time notation. concat(outputs('Compose_Next_date_of_chosen_day_of_week'), 'T', outputs('Compose_Convert_to_UTC_timezone'), 'Z') ","metadata":{"operationMetadataId":"217b74f8-2bf2-493b-97fe-39363813513d"}}},"runAfter":{}}}

takolota · ‎07-09-2022

Anyone who needs this to work in your specific timezone, use this version & set-up:

{"id":"596c33eb-d655-42d8-867a-7adf4eb7f3a1","brandColor":"#8C3900","connectionReferences":{},"connectorDisplayName":"Control","icon":"data&colon;image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Date_next_day_of_week_Delay_until","operationDefinition":{"type":"Scope","actions":{"Scope_Get_date_of_next_chosen_day_of_the_week":{"type":"Scope","actions":{"Compose_Choose_the_desired_day_of_week_number":{"type":"Compose","inputs":1,"runAfter":{"Compose_Your_timezone":["Succeeded"]},"description":"1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday, 7 = Sunday","metadata":{"operationMetadataId":"a09413a3-9fd7-4fd8-815e-0678a12da59a"}},"Compose_Next_date_of_chosen_day_of_week":{"type":"Compose","inputs":"@adddays(convertFromUtc(utcNow(), outputs('Compose_Your_timezone')), \r\nif(greater(sub(dayofweek(convertFromUtc(utcNow(), outputs('Compose_Your_timezone'))), outputs('Compose_Choose_the_desired_day_of_week_number')),outputs('Compose_Activity_on_chosen_day_of_week')), \r\nadd(7, sub(outputs('Compose_Choose_the_desired_day_of_week_number'), dayofweek(convertFromUtc(utcNow(), outputs('Compose_Your_timezone'))))), \r\nint(replace(string(sub(dayofweek(convertFromUtc(utcNow(), outputs('Compose_Your_timezone'))), outputs('Compose_Choose_the_desired_day_of_week_number'))), '-', ''))), \r\n'yyyy-MM-dd')","runAfter":{"Compose_Activity_on_chosen_day_of_week":["Succeeded"]},"description":"adddays(utcNow(), if(greater(sub(dayofweek(utcNow()), outputs('Compose')),0), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')","metadata":{"operationMetadataId":"5ac576b8-c9d3-42c1-bf38-f877ea2590ed"}},"Compose_Activity_on_chosen_day_of_week":{"type":"Compose","inputs":0,"runAfter":{"Compose_Choose_the_desired_day_of_week_number":["Succeeded"]},"description":"If the current day is the chosen day of the week, should it output the current date or the same weekday's date 1 week from now? Enter 0 to output the current day's date on the chosen weekday and enter -1 to output the weekday's date 1 week later.","metadata":{"operationMetadataId":"bb242943-793c-4791-9137-1f991ddb14ad"}},"Compose_Your_timezone":{"type":"Compose","inputs":"Eastern Standard Time","runAfter":{},"description":"Some expressions must convert UTC time to your timezone. Change 'Eastern Standard Time' to your timezone below. List of timezone tags: https://docs.microsoft.com/en-us/previous-versions/windows/embedded/ms912391(v=winembedded.11)","metadata":{"operationMetadataId":"5db08ec3-ab29-4fc0-a69d-c8b6164ea963"}}},"runAfter":{},"metadata":{"operationMetadataId":"bd9d9703-1abe-4b07-a4f1-b68604ff84d0"}},"Compose_Delay_time_of_day":{"type":"Compose","inputs":"20:45","runAfter":{"Scope_Get_date_of_next_chosen_day_of_the_week":["TimedOut"]},"description":"What hour & minutes do you want to Delay until, in your timezone, on a 24-hour clock?","metadata":{"operationMetadataId":"150843f2-cb78-42ab-8cd1-8b74a3f4b449"}},"Compose_Convert_to_UTC_timezone":{"type":"Compose","inputs":"@convertToUtc(concat(outputs('Compose_Next_date_of_chosen_day_of_week'), 'T', outputs('Compose_Delay_time_of_day')), outputs('Compose_Your_timezone'))","runAfter":{"Compose_Delay_time_of_day":["Succeeded"]},"description":"Concatenate the calculated date with the chosen time & the proper time notation. convertToUtc(concat(outputs('Compose_Next_date_of_chosen_day_of_week'), 'T', outputs('Compose_Delay_time_of_day'), 'Z'), outputs('Compose_Your_timezone'))","metadata":{"operationMetadataId":"5db08ec3-ab29-4fc0-a69d-c8b6164ea963"}},"Delay_until":{"type":"Wait","inputs":{"until":{"timestamp":"@{outputs('Compose_Convert_to_UTC_timezone')}"}},"runAfter":{"Compose_Convert_to_UTC_timezone":["Succeeded"]},"description":"Delay until actions require input time to be in UTC time.","metadata":{"operationMetadataId":"217b74f8-2bf2-493b-97fe-39363813513d"}}},"runAfter":{},"metadata":{"operationMetadataId":"052fb1e1-6851-476c-8864-3af3b48ac5f3"}}}

CallumFlett · ‎08-08-2022

You have saved me a lot of time with this template.

Thanks!

breid · ‎11-10-2022

Good afternoon,

Can I use this template to get the next Friday of a future Date? I need the friday after a SPList date column value as the paydate for the flow.

takolota · ‎11-10-2022

@breid

You would have to change out utcnow() for the given column values on that one.

adddays(utcNow(), if(greater(sub(dayofweek(utcNow()), outputs('Compose')),0), add(7, sub(outputs('Compose'), dayofweek(utcNow()))), int(replace(string(sub(dayofweek(utcNow()), outputs('Compose'))), '-', ''))), 'yyyy-MM-dd')

So I would suggest adding a compose action & inputting the dynamic content there, then switching each utcnow() with that compose output.