<article class="post-13797 post type-post status-publish format-standard has-post-thumbnail category-power-platform category-uncategorized tag-power-automate entry genesis-feature genesis-feature-1 genesis-feature-odd" aria-label="Add working days to a date in a Power Automate Flow"><header class="entry-header"><h2 class="entry-title"><a class="entry-title-link" rel="bookmark" href="https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/">Add working days to a date in a Power Automate Flow</a></h2> <p class="entry-meta"><time class="entry-time">April 5, 2022</time> by <span class="entry-author"><a href="https://www.tachytelic.net/author/paulie/" class="entry-author-link" rel="author"><span class="entry-author-name">Paulie</span></a></span> <span class="entry-comments-link"><a href="https://www.tachytelic.net/2022/04/power-automate-flow-add-working-days-date/#comments">5 Comments</a></span> </p></header><div class="entry-content"> <p>In this post I will show you a Power Automate Flow you can use to add days to a given date and exclude weekends, or specific dates from the result. I was inspired to do my own solution by some recent posts by others:</p> <p>Tom Riha – <a href="https://tomriha.com/how-to-add-only-working-days-to-a-date-using-power-automate/">How to add only working days to a date using Power Automate</a><br>Matthew Devaney – <a href="https://www.matthewdevaney.com/power-apps-calculate-business-days-excluding-weekends-holidays/" target="_blank" rel="noreferrer noopener">Power Apps Calculate Business Days Excluding Weekends & Holidays</a></p> <p>Both of these guys have brilliant blogs and I highly recommend subscribing to both of them. It is really interesting to see how we all approached the problem slightly differently.</p> <p>Power Automate includes the useful expression <a href="https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#addDays" target="_blank" rel="noreferrer noopener"><strong>addDays</strong></a> which will add a specified number of days to a date that you provide. While this function is useful, it does not allow you to exclude certain days. Such as weekends or public holidays. </p> <h2 id="h-basic-flow-add-days-excluding-weekends">Basic Flow – Add days excluding weekends</h2> <p>First, add two compose actions, name the first one <strong>Date</strong> and the second one <strong>DaysToAdd</strong>.</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img width="762" height="306" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-1.png" alt="Power Automate Compose Actions to setup the date additions." class="wp-image-13799" srcset="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-1.png 762w, https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-1-300x120.png 300w" sizes="(max-width: 762px) 100vw, 762px" /></figure></div> <p>Next we will generate a range of dates for the next 365 days and also what day of the week they represent. Add a <strong>Select</strong> action and name it <strong>Dates</strong>:</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="755" height="314" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-2-1.png" alt="" class="wp-image-13811" srcset="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-2-1.png 755w, https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-2-1-300x125.png 300w" sizes="(max-width: 755px) 100vw, 755px" /></figure></div> <p>The expressions used in the select:</p> <pre class="wp-block-preformatted"><strong>From: </strong>range(0,365) <strong>Date:</strong> formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd') <strong>Day:</strong> dayOfWeek(addDays(outputs('Date'),item()))</pre> <p>A sample of the output of the select action will be a range of 365 dates, something like this:</p> <pre class="EnlighterJSRAW" data-enlighter-language="json" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="false" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">[ { "Date": "2022-04-04", "Day": 1 }, { "Date": "2022-04-05", "Day": 2 }, { "Date": "2022-04-06", "Day": 3 }, { "Date": "2022-04-07", "Day": 4 }, { "Date": "2022-04-08", "Day": 5 }, { "Date": "2022-04-09", "Day": 6 } ]</pre> <p>As you can see, we are generating an array of dates, and the day of the week that date represents (0 being Sunday and 6 being Saturday).</p> <p>Next, add a <strong>Filter Array</strong> step, to reduce the dates array so that it only includes week days:</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="761" height="228" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-3.png" alt="" class="wp-image-13803" srcset="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-3.png 761w, https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-3-300x90.png 300w" sizes="(max-width: 761px) 100vw, 761px" /></figure></div> <p>The expression used in the Filter is:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="false" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))</pre> <p>Now we have a date array that contains only weekdays, we just need to pick out our target date. Add a compose action called <strong>TargetDate</strong>:</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="753" height="189" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-4.png" alt="" class="wp-image-13805" srcset="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-4.png 753w, https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Days-Step-4-300x75.png 300w" sizes="(max-width: 753px) 100vw, 753px" /></figure></div> <p>The expression used here is:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="false" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">body('Filter_array')[outputs('DaysToAdd')]['date']</pre> <p>This expression will return just the date that we are looking to find, here is the output from my example:</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="758" height="468" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Days-Added-To-Date-1.png" alt="" class="wp-image-13815" srcset="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Days-Added-To-Date-1.png 758w, https://www.tachytelic.net/wp-content/uploads/Power-Automate-Days-Added-To-Date-1-300x185.png 300w" sizes="(max-width: 758px) 100vw, 758px" /></figure></div> <p>The <strong>TargetDate</strong> compose action is optional, you can simply use the expression shown in any of your actions.</p> <h2 id="h-complete-code-for-basic-example">Complete Code for Basic Example</h2> <p>If you prefer, you can simply copy the code below into your clipboard and paste it into your own flow:</p> <pre class="EnlighterJSRAW" data-enlighter-language="latex" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="false" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">{ "id": "df633c17-f74d-48fa-bd16-e67fbcc8eeef", "brandColor": "#8C3900", "connectionReferences": {}, "connectorDisplayName": "Control", "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=", "isTrigger": false, "operationName": "Add_Days", "operationDefinition": { "type": "Scope", "actions": { "Date": { "type": "Compose", "inputs": "2022-04-04", "runAfter": {}, "metadata": { "operationMetadataId": "f03622ae-0e01-4a52-957b-476f810aea4d" } }, "DaysToAdd": { "type": "Compose", "inputs": 10, "runAfter": { "Date": ["Succeeded"] }, "metadata": { "operationMetadataId": "22b2d012-3447-4ac0-80a4-24251d06ff99" } }, "Dates": { "type": "Select", "inputs": { "from": "@range(0,365)", "select": { "Date": "@formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')", "Day": "@dayOfWeek(addDays(outputs('Date'),item()))" } }, "runAfter": { "DaysToAdd": ["Succeeded"] }, "description": "range(0,365)", "metadata": { "operationMetadataId": "362d7f26-03cb-4a8c-a70e-8e75019a1061" } }, "Filter_array": { "type": "Query", "inputs": { "from": "@body('Dates')", "where": "@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))" }, "runAfter": { "Dates": ["Succeeded"] }, "metadata": { "operationMetadataId": "bc17d8a4-b5b8-4a5b-bd3a-34fcd2d0f2ae" } }, "TargetDate": { "type": "Compose", "inputs": "@body('Filter_array')[outputs('DaysToAdd')]['date']", "runAfter": { "Filter_array": ["Succeeded"] }, "description": "body('Filter_array')[outputs('DaysToAdd')]['date']", "metadata": { "operationMetadataId": "19be198e-647c-40bf-9109-86261450a9ef" } } }, "runAfter": {}, "metadata": { "operationMetadataId": "ff58b7e7-a16e-4a1c-9ffe-b377e6b228ae" } } }</pre> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="700" height="874" src="https://www.tachytelic.net/wp-content/uploads/Power-Automate-Add-Working-Days.gif" alt="" class="wp-image-13808"/></figure></div> <h2 id="h-add-filtering-of-public-holidays">Add Filtering of Public Holidays</h2> <p>It is possible to enhance the flow above so that it will also exclude public holidays in addition to the weekends.</p> <p>First, setup a SharePoint list that contains all of the dates you want to skip. I setup my SharePoint list with two columns. <strong>Title</strong> and <strong>HolidayDay</strong>. This is how my list looks:</p> <div class="wp-block-image"><figure class="aligncenter size-full"><img loading="lazy" width="496" height="289" src="https://www.tachytelic.net/wp-content/uploads/UK-Public-Holidays.png" alt="SharePoint list showing public holidays which will be excluded from the result." class="wp-image-13813" srcset="https://www.tachytelic.net/wp-content/uploads/UK-Public-Holidays.png 496w, https://www.tachytelic.net/wp-content/uploads/UK-Public-Holidays-300x175.png 300w" sizes="(max-width: 496px) 100vw, 496px" /></figure></div> <p>Next we can modify our flow to also exclude these dates. We need to add an additional three actions:</p> <p>First, add a <strong>Get Items</strong> (SharePoint) action right below the existing Filter Array action and point it at your site and your Holiday Calendar list.</p> <p>Then add a <strong>Select</strong> action, rename it to HolidayDays. Modify the select action so that it is in text mode by clicking the small grid icon on the right side of the <strong>Map</strong> field. Populate the <strong>From</strong> field with the <strong>value</strong> from the Get Items action. In the <strong>Map</strong> field choose the HolidayDay.</p> <p>Next add a filter action and rename it to <strong>ExcludeHolidays</strong>. The <strong>from</strong> will be the body output of our original filter array. On the left side of the filter we choose the output from the <strong>HolidayDays</strong> action and choose the option <strong>does not contain</strong> on the filter. On the right hand side of the filter use the expression <strong>item()[‘Date’]</strong> – It is much easier to comprehend in a image:</p> <div class="wp-block-image"><figure class="aligncenter size-large"><img loading="lazy" width="735" height="1024" src="https://www.tachytelic.net/wp-content/uploads/ExcludeHolidays-735x1024.png" alt="" class="wp-image-13814" srcset="https://www.tachytelic.net/wp-content/uploads/ExcludeHolidays-735x1024.png 735w, https://www.tachytelic.net/wp-content/uploads/ExcludeHolidays-215x300.png 215w, https://www.tachytelic.net/wp-content/uploads/ExcludeHolidays.png 755w" sizes="(max-width: 735px) 100vw, 735px" /></figure></div> <p>Finally, modify the expression in the <strong>TargetDate</strong> compose action to:</p> <pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="false" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">body('ExcludeHolidays')[outputs('DaysToAdd')]['date']</pre> <p>Now your <strong>TargetDate</strong> output will exclude both weekends and any public holidays that you have specified.</p> <p></p> </div><footer class="entry-footer"><p class="entry-meta"><span class="entry-categories">Filed Under: <a href="https://www.tachytelic.net/category/power-platform/" rel="category tag">Power Platform</a>, <a href="https://www.tachytelic.net/category/uncategorized/" rel="category tag">Uncategorized</a></span> <span class="entry-tags">Tagged With: <a href="https://www.tachytelic.net/tag/power-automate/" rel="tag">Power Automate</a></span></p></footer></article><article class="post-13727 post type-post status-publish format-standard has-post-thumbnail category-how-to entry genesis-grid genesis-grid-1 genesis-grid-odd" aria-label="Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990"><header class="entry-header"><h2 class="entry-title"><a class="entry-title-link" rel="bookmark" href="https://www.tachytelic.net/2022/02/dell-optiplex-790-990-nvme/">Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990</a></h2> <p class="entry-meta"><time class="entry-time">February 13, 2022</time> by <span class="entry-author"><a href="https://www.tachytelic.net/author/paulie/" class="entry-author-link" rel="author"><span class="entry-author-name">Paulie</span></a></span> <span class="entry-comments-link"><a href="https://www.tachytelic.net/2022/02/dell-optiplex-790-990-nvme/#comments">19 Comments</a></span> </p></header><div class="entry-content"><a href="https://www.tachytelic.net/2022/02/dell-optiplex-790-990-nvme/"><img width="270" height="100" src="https://www.tachytelic.net/wp-content/uploads/Dell-Optiplex-790-NVME-270x100.png" class="grid-featured" alt="" loading="lazy" /></a><p>In this post I will show you how you can modify the BIOS of a Dell OptiPlex 790 or 990 so that it can support an NVMe SSD drive as a boot device. Although these machines have a UEFI BIOS, they do not contain an NVMe driver. By adding the driver … <a href="https://www.tachytelic.net/2022/02/dell-optiplex-790-990-nvme/" class="more-link">[Continue reading] <span class="screen-reader-text">about Install and boot from an NVMe SSD on a Dell OptiPlex 790 or 990</span></a></p></div><footer class="entry-footer"><p class="entry-meta"><span class="entry-categories">Filed Under: <a href="https://www.tachytelic.net/category/how-to/" rel="category tag">How To</a></span> </p></footer></article><article class="post-13653 post type-post status-publish format-standard has-post-thumbnail category-how-to entry genesis-grid genesis-grid-2 genesis-grid-even" aria-label="Install and boot from an NVMe SSD on a HP Compaq Elite 8300 SFF"><header class="entry-header"><h2 class="entry-title"><a class="entry-title-link" rel="bookmark" href="https://www.tachytelic.net/2022/01/hp-compaq-8300-nvme/">Install and boot from an NVMe SSD on a HP Compaq Elite 8300 SFF</a></h2> <p class="entry-meta"><time class="entry-time">January 11, 2022</time> by <span class="entry-author"><a href="https://www.tachytelic.net/author/paulie/" class="entry-author-link" rel="author"><span class="entry-author-name">Paulie</span></a></span> <span class="entry-comments-link"><a href="https://www.tachytelic.net/2022/01/hp-compaq-8300-nvme/#comments">9 Comments</a></span> </p></header><div class="entry-content"><a href="https://www.tachytelic.net/2022/01/hp-compaq-8300-nvme/"><img width="270" height="100" src="https://www.tachytelic.net/wp-content/uploads/HP-Compaq-8300-Elite-NVMe-270x100.png" class="grid-featured" alt="" loading="lazy" /></a><p>In this post I will show you how you can install an NVMe SSD into a HP Compaq Elite 8300 Small Form Factor and modify the BIOS so that the machine can boot natively from it. The 8300 SFF does have a UEFI BIOS, but there is no NVMe driver … <a href="https://www.tachytelic.net/2022/01/hp-compaq-8300-nvme/" class="more-link">[Continue reading] <span class="screen-reader-text">about Install and boot from an NVMe SSD on a HP Compaq Elite 8300 SFF</span></a></p></div><footer class="entry-footer"><p class="entry-meta"><span class="entry-categories">Filed Under: <a href="https://www.tachytelic.net/category/how-to/" rel="category tag">How To</a></span> </p></footer></article><article class="post-13576 post type-post status-publish format-standard has-post-thumbnail category-how-to entry genesis-grid genesis-grid-3 genesis-grid-odd" aria-label="Install and boot from an NVMe SSD on a Dell OptiPlex 3010, 7010 or 9010"><header class="entry-header"><h2 class="entry-title"><a class="entry-title-link" rel="bookmark" href="https://www.tachytelic.net/2021/12/dell-optiplex-7010-pcie-nvme/">Install and boot from an NVMe SSD on a Dell OptiPlex 3010, 7010 or 9010</a></h2> <p class="entry-meta"><time class="entry-time">December 26, 2021</time> by <span class="entry-author"><a href="https://www.tachytelic.net/author/paulie/" class="entry-author-link" rel="author"><span class="entry-author-name">Paulie</span></a></span> <span class="entry-comments-link"><a href="https://www.tachytelic.net/2021/12/dell-optiplex-7010-pcie-nvme/#comments">104 Comments</a></span> </p></header><div class="entry-content"><a href="https://www.tachytelic.net/2021/12/dell-optiplex-7010-pcie-nvme/"><img width="270" height="100" src="https://www.tachytelic.net/wp-content/uploads/Dell-Optiplex-7010-NVMe-SSD-Upgrade-270x100.png" class="grid-featured" alt="" loading="lazy" /></a><p>In this post I will show you how you can modify the BIOS of a Dell OptiPlex 3010, 7010 or 9010 so that it can support an NVMe SSD drive as a boot device. This is a follow up to a post I did on how boot a Dell OptiPlex 7020 with an NVMe drive. I … <a href="https://www.tachytelic.net/2021/12/dell-optiplex-7010-pcie-nvme/" class="more-link">[Continue reading] <span class="screen-reader-text">about Install and boot from an NVMe SSD on a Dell OptiPlex 3010, 7010 or 9010</span></a></p></div><footer class="entry-footer"><p class="entry-meta"><span class="entry-categories">Filed Under: <a href="https://www.tachytelic.net/category/how-to/" rel="category tag">How To</a></span> </p></footer></article><article class="post-13531 post type-post status-publish format-standard has-post-thumbnail category-how-to entry genesis-grid genesis-grid-4 genesis-grid-even" aria-label="Install and boot from an NVMe SSD on a Dell OptiPlex 9020, 7020 or 3020"><header class="entry-header"><h2 class="entry-title"><a class="entry-title-link" rel="bookmark" href="https://www.tachytelic.net/2021/12/dell-optiplex-7020-nvme-ssd/">Install and boot from an NVMe SSD on a Dell OptiPlex 9020, 7020 or 3020</a></h2> <p class="entry-meta"><time class="entry-time">December 25, 2021</time> by <span class="entry-author"><a href="https://www.tachytelic.net/author/paulie/" class="entry-author-link" rel="author"><span class="entry-author-name">Paulie</span></a></span> <span class="entry-comments-link"><a href="https://www.tachytelic.net/2021/12/dell-optiplex-7020-nvme-ssd/#comments">75 Comments</a></span> </p></header><div class="entry-content"><a href="https://www.tachytelic.net/2021/12/dell-optiplex-7020-nvme-ssd/"><img width="270" height="100" src="https://www.tachytelic.net/wp-content/uploads/Dell-Optiplex-9020-7020-3020-NVMe-SSD-Upgrade-Blog-270x100.png" class="grid-featured" alt="" loading="lazy" /></a><p>In this post I will show you how you can modify the BIOS of your Dell OptiPlex 9020, 7020 or 3020 so that they can support an NVMe SSD drive as a boot device. These machines have a UEFI BIOS, but they do not contain an NVMe driver. By adding the … <a href="https://www.tachytelic.net/2021/12/dell-optiplex-7020-nvme-ssd/" class="more-link">[Continue reading] <span class="screen-reader-text">about Install and boot from an NVMe SSD on a Dell OptiPlex 9020, 7020 or 3020</span></a></p></div><footer class="entry-footer"><p class="entry-meta"><span class="entry-categories">Filed Under: <a href="https://www.tachytelic.net/category/how-to/" rel="category tag">How To</a></span> </p></footer></article><div class="archive-pagination pagination" role="navigation" aria-label="Pagination"><ul><li class="active" ><a href="https://www.tachytelic.net/?subscribe=success&gppro-loggedout=1&gppro-preview=1" aria-label="Current page" aria-current="page"><span class="screen-reader-text">Go to page</span> 1</a></li> <li><a href="https://www.tachytelic.net/page/2/?subscribe=success&gppro-loggedout=1&gppro-preview=1"><span class="screen-reader-text">Go to page</span> 2</a></li> <li><a href="https://www.tachytelic.net/page/3/?subscribe=success&gppro-loggedout=1&gppro-preview=1"><span class="screen-reader-text">Go to page</span> 3</a></li> <li class="pagination-omission"><span class="screen-reader-text">Interim pages omitted</span> …</li> <li><a href="https://www.tachytelic.net/page/34/?subscribe=success&gppro-loggedout=1&gppro-preview=1"><span class="screen-reader-text">Go to page</span> 34</a></li> <li class="pagination-next"><a href="https://www.tachytelic.net/page/2/?subscribe=success&gppro-loggedout=1&gppro-preview=1" ><span class="screen-reader-text">Go to</span> Next Page »</a></li> </ul></div>