• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

Uncategorized

Add working days to a date in a Power Automate Flow

April 5, 2022 by Paulie 5 Comments

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:

Tom Riha – How to add only working days to a date using Power Automate
Matthew Devaney – Power Apps Calculate Business Days Excluding Weekends & Holidays

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.

Power Automate includes the useful expression addDays 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.

Basic Flow – Add days excluding weekends

First, add two compose actions, name the first one Date and the second one DaysToAdd.

Power Automate Compose Actions to setup the date additions.

Next we will generate a range of dates for the next 365 days and also what day of the week they represent. Add a Select action and name it Dates:

The expressions used in the select:

From: range(0,365)
Date: formatDateTime(addDays(outputs('Date'),item()), 'yyyy-MM-dd')
Day: dayOfWeek(addDays(outputs('Date'),item()))

A sample of the output of the select action will be a range of 365 dates, something like this:

[
  {
    "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
  }
]

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).

Next, add a Filter Array step, to reduce the dates array so that it only includes week days:

The expression used in the Filter is:

@and(not(equals(item()['day'], 0)), not(equals(item()['day'], 6)))

Now we have a date array that contains only weekdays, we just need to pick out our target date. Add a compose action called TargetDate:

The expression used here is:

body('Filter_array')[outputs('DaysToAdd')]['date']

This expression will return just the date that we are looking to find, here is the output from my example:

The TargetDate compose action is optional, you can simply use the expression shown in any of your actions.

Complete Code for Basic Example

If you prefer, you can simply copy the code below into your clipboard and paste it into your own flow:

{
	"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"
		}
	}
}

Add Filtering of Public Holidays

It is possible to enhance the flow above so that it will also exclude public holidays in addition to the weekends.

First, setup a SharePoint list that contains all of the dates you want to skip. I setup my SharePoint list with two columns. Title and HolidayDay. This is how my list looks:

SharePoint list showing public holidays which will be excluded from the result.

Next we can modify our flow to also exclude these dates. We need to add an additional three actions:

First, add a Get Items (SharePoint) action right below the existing Filter Array action and point it at your site and your Holiday Calendar list.

Then add a Select 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 Map field. Populate the From field with the value from the Get Items action. In the Map field choose the HolidayDay.

Next add a filter action and rename it to ExcludeHolidays. The from will be the body output of our original filter array. On the left side of the filter we choose the output from the HolidayDays action and choose the option does not contain on the filter. On the right hand side of the filter use the expression item()[‘Date’] – It is much easier to comprehend in a image:

Finally, modify the expression in the TargetDate compose action to:

body('ExcludeHolidays')[outputs('DaysToAdd')]['date']

Now your TargetDate output will exclude both weekends and any public holidays that you have specified.

Filed Under: Power Platform, Uncategorized Tagged With: Power Automate

Fixing Headset overload with the Jabra Evolve 75 wireless

October 30, 2019 by Paulie 2 Comments

I spend a lot of time on the phone, and I cannot stand to hold the phone to my head, unless I can talk on a headset, I really don’t want to talk. So I bought myself a Jabra Evolve 75 to set about clearing the cables off my desk.

The problem is I have a headset for every device, this is my desk:

Image of office desk covered in wired headsets

I’ve got three headsets and a conference speaker:

  • A Microsoft Lifechat LX-6000, which I use mainly for Skype and Microsoft Teams calls.
  • A Cheap wired Jabra that I plug into my iPhone XS.
  • A Wireless Platronics CS510 that I used with my Cisco SPA 525G Deskphone.
  • A Jabra Speak 510

All the devices work well , but my desk is a mess, the wires get tangled and I pick up the wrong headset occasionally.

So I decided to buy the Jabra Evolve 75 and get rid of every other headset. The desk phone I am going to remove completely and replace it with Bria 5 Softphone from Counterpath.

I bought the headset package which came included with the charging dock:

Image showing contents of box of Jabra Evolve 75. Charging docks, documentation, headset case and headset itself.

Box Contents

In the box there is:

  • A charging dock (powered by USB)
  • Some documentation.
  • The Jabra Evolve 75 Headset Itself.
  • A decent quality case to protect the headset.

The case has a neat little slot for the USB receiver and a sleek little pocket for holding the charge cable.

Jabra Evolve 75 Dongle Next to a One Euro Coin

Setup

It would be impossible to make the setup up the Jabra Evolve 75 any easier. You just plug the tiny dongle into your PC/Laptop, and it is ready to use a couple of seconds later. The headset provides an audible notification that it is connected to the PC. That’s it!

Connection to your smart phone is just as easy, push a little switch on the headset for a couple of seconds and the headset informs you that it is ready to pair, select it from your phone and it’s done.

Jabra Evolve 75 Sound Quality

I was expecting the Jabra headset to sound decent, but it far exceeded my expectations:

  • Call quality is exceptional. I tried it with a SIP call, Skype and normal cellular call.
  • The quality for music and movies is absolutely brilliant. The trailer for The Last Jedi sounded brilliant.

Active Noise Cancellation

My office is very quiet, so the noise cancellation functionality was not immediately useful to me. But for making calls in the car, it is nothing short of amazing.

I drive a Golf GTI Mk7 and the road noise/tyre roar is terrible. It really undermines the in-car Bluetooth system because it’s nigh on impossible to hear what anyone is saying.

So firstly, without making a call I put the headset on and just experimented with switching ANC on and off. It was only when I did that I realised just how noisy the Golf is, the Jabra virtually eliminates the low end rumble the car produces. Even without making a call it makes the car a more pleasant place to be.

I made a few calls and chatted for around half an hour to a couple of different people. Neither caller was able to tell I was in the car, on the motorway. I could hear them with complete clarity and the same was true the other way around. It’s impossible to articulate how well it worked in the car unless you can hear it for yourself, but it’s really very impressive.

Other Nice Touches

There are some other nice functions of the headset that are worthy of mention:

  • It automatically mutes the calls when you put the microphone into the vertical position.
  • The Microphone band magnetically snaps on to the head band when it’s in the vertical position.
  • It has a red band around the Jabra logo that lights up when you are on a call, so people can see if you are on the phone.
  • The mute button on the right ear piece can be used to activate Siri when you are not in a call, which is really handy.

A longer USB cable on the charging dock would be nice, but it is only a minor complaint.

I highly recommend the Jabra Evolve 75, it has made my working environment much nicer and I will use it in ways that I wouldn’t expect to use a “business” headset for. Just look how tidy my desk is now!

Image of a desk with a monitor, keyboard, mouse etc. Shows the difference in cables when switching to a wireless headset, in this case the Jabra Evolve 75.

Filed Under: Uncategorized

Tips for a Tidy Communications Cabinet

September 29, 2019 by Paulie Leave a Comment

A cluttered communications cabinet is unpleasant to look at, confusing and difficult to work with. I’ve had to tidy up a few recently and it is a strangely satisfying task. Here are a few tips and some photos that show my basic approach.

  • Use short cables between the patch panel and the network switches.
  • Place 48-Port switches between port patch panels.
  • Use Velcro ties to bind groups of cables together.
  • Photo of untidy communications cabinet
    Before
  • photo of tidy communications cabinet
    After

As you can see, the layout before was really difficult to work with, mainly due to the patch cable lengths. It was setup as follows (from top to bottom):

  • 2 x 24 Port Patch Panels
  • 1 x Cable Management Bar
  • 1 x 24 Port Patch Panel
  • 2 x 48 Port Netgear Switches
  • 1 x Cable Management Bar

Even with shorter cables this layout would have still been a mess because so much unnecessary routing would be required. By moving the switches to sit between the patch panels you can patch every port without needing to route the cables anywhere other than the switch directly below or above it. In this example the switches were also swapped out for 48-Port UniFi PoE Switches. The patch cables are 25cm.

The next example isn’t as visually pleasing, because:

  • The cabinet is too small. I couldn’t fit the required number of cable management bars.
  • The existing analogue phone system forces patching to particular ports.
  • I just didn’t have enough time.
  • Before
  • During
  • After

It’s still an improvement and easier to work with, but I wasn’t completely happy with it. Hopefully they will be switching over to a 3CX VoIP system soon so at that point it can be completely reorganised.

The final one didn’t have a before photo, but it turned out simple and nice:

Image of UniFi Switches in Communications cabinet patched with 25cm Yellow cables.
There are 7 UniFi Switches in total to cover every port in the building

So if possible, make sure you have:

  • Plenty of space.
  • Lots of different cable lengths.
  • Make effective use of cable management bars.

Let me know your ideas in the comments.

Filed Under: Uncategorized

VPN connections do not work after Windows 10 May 2019 Update

June 13, 2019 by Paulie 51 Comments

I received the latest Windows 10 update today (May 2019 – Build 1903) and found that none of my existing VPN connections worked. When I try to connect to one of them, it just says “connecting” and eventually stops without producing an error.

I have not found a solution to this, but I have found a very easy workaround. This is by using a tool that has been in Windows Since at least Windows NT 4.0 Called rasphone.exe. I actually prefer the simple interface it provides.

How to get access to your existing VPN connecitons

  • Create a new shortcut on your desktop to:
    C:\Windows\System32\rasphone.exe
  • Double click your new shortcut and you should get this interface:
    Image showing rasphone.exe running on Windows 10 1903 update
  • Try your VPN connection.

This will give you access to the traditional dial-up tool which seems to work perfectly. It’s quite clear that something in the update is blocking access to the credentials window appearing and therefore the connection never completes.

It seems strange to workaround an issue with the very latest version of Windows 10 by using a tool that has been around for over 20 years!

Filed Under: Uncategorized Tagged With: Windows 10

Plantronics CS510/CS520 with Cisco SPA 504G and Cisco SPA 525G

July 27, 2018 by Paulie Leave a Comment

We’ve been using Cisco SPA Series IP Phones for a long time in our Office, mainly we now use the 504 and 525G models. You need a wireless headset if you are going to spend a significant amount of time on the phone – and we have settled on the Plantronics CS510 for our Cisco SPA Devices.

Plantronics CS500 Series general information

There are four variants in the CS500 range:

  • CS510 (Pictured in this review)
  • CS520 (Same as 510 but covers both ears)
  • CS530 (Over the ear)
  • CS540 (Over the ear or over the head)

All of the models are compatible with the Cisco SPA Series by using various accessories that Plantronics make available.

The CS510 and CS520 are the same but the CS510 has only one ear piece and the CS520 covers both ears. If your office is noisy then the CS520 is probably a better choice.

I’ve never tried the 530 or 540 as I spend a lot of time on the phone and just feel like over the head is more comfortable for long conversations.

Plantronics CS510 Wireless Headset with Cisco SPA504G

The Cisco SPA504G works perfectly well with the CS510, but it requires the use of a handset lifter – the Savi HL10. This is a mechanical device which actually lifts the handset off the telephone in order to answer calls and make the headset active.

Video showing the handset being lifted by the HL10 on a Cisco SPA 504G

To use the Plantronics CS500 Series with the Cisco SPA 504G, you need:

  • [amazon_textlink asin=’B0006B088W’ text=’Plantronics HL-10 Handset Lifter’ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’bd907647-9181-11e8-9d0b-97a473f00b81′]
  • And your choice of CS500 Model:
    • [amazon_textlink asin=’B005HI2NYQ’ text=’Plantronics CS510′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’eb299674-9181-11e8-90eb-3571bb358a9a’]
    • [amazon_textlink asin=’B005HI2NX2′ text=’Plantronics CS520′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’f5d73371-9181-11e8-991e-69ad6bbbaaf2′]
    • [amazon_textlink asin=’B0082AYBNM’ text=’Plantronics CS530′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’077600df-9182-11e8-b53f-93ea3a1467b4′]
    • [amazon_textlink asin=’B005HI2ME2′ text=’Plantronics CS540′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’12c89097-9182-11e8-b60d-ab94cd843b07′]
  • And of course the [amazon_textlink asin=’B002HXW984′ text=’Cisco SPA504G’ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’98b9c0ce-9188-11e8-9421-7d1d2c7cf90a’]
Cisco SPA 504G with Plantronics CS10 Wireless Headset
Cisco SPA 504G with Plantronics CS10 Wireless Headset in charging dock

Plantronics CS510 Wireless Headset with Cisco SPA525G

The Cisco SPA 525G with the Plantronics CS510  is an elegant solution as there is no need for a handset lifter because it has support for an Electronic Hook Switch (EHS).

But you do require a special cable from Plantronics, which is called the [amazon_textlink asin=’B008D6RSEY’ text=’APC-45′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’4763307b-9189-11e8-9467-4b8dad5d2c72′] to connect the CS510 to the phone.

Cisco SPA525G with Plantronics CS510 Wireless Headset
Cisco SPA525G with Plantronics CS510 Wireless Headset

To use the Cisco SPA525G with the Plantronics CS500 Wireless Headset Series, you need:

  • Choice of CS500 Model:
    • [amazon_textlink asin=’B005HI2NYQ’ text=’Plantronics CS510′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’eb299674-9181-11e8-90eb-3571bb358a9a’]
    • [amazon_textlink asin=’B005HI2NX2′ text=’Plantronics CS520′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’f5d73371-9181-11e8-991e-69ad6bbbaaf2′]
    • [amazon_textlink asin=’B0082AYBNM’ text=’Plantronics CS530′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’077600df-9182-11e8-b53f-93ea3a1467b4′]
    • [amazon_textlink asin=’B005HI2ME2′ text=’Plantronics CS540′ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’12c89097-9182-11e8-b60d-ab94cd843b07′]
  • [amazon_textlink asin=’B008D6RSEY’ text=’APC-45 Connection Cable’ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’8e495e6a-9189-11e8-9960-254d24cbb1a9′]
  • [amazon_textlink asin=’B001PI9QOW’ text=’Cisco SPA525G’ template=’ProductLink’ store=’tachytelicnet-21′ marketplace=’UK’ link_id=’9f7e94ce-9189-11e8-aef2-6dca01bb821b’]

Call Quality and Battery Life

I spend about 3-4 hours a day on the phone and have never run out of battery, probably because I store the headset on the docking station between calls, so it is always kept topped up.

Wireless range is really good. I have not tested it to extremes, but I often walk at least 20 meters away without any issues.

We have been using the CS510 for years and it has been reliable and comfortable. I never use the standard handset from the phone as I find it incredibly uncomfortable.

Having the headset leaves both hands free for keyboard use and because it is wireless, there are no annoying cables distracting you.

Filed Under: Uncategorized

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile
Buy me a coffee

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 245 other subscribers.

Go to mobile version