Assuming you already have a Home Assistant server humming away quietly, the most important thing you’ll need for this to work is a smart plug that doesn’t rely on Wi-Fi. Because we’re restarting your router, plugs that depend on a wireless network connection won’t be able to receive the trigger to switch back on when your automation runs.

I’ve got a Zigbee network that uses IKEA smart plugs, but you could also use Matter over Thread, Z-Wave, or even a Bluetooth smart plug as long as it’s within range of a nearby adapter. We’ll be using the smart plug to power cycle our network equipment, and any non-Wi-Fi smart plug should work as long as your server remains up and running.

Though it’s not necessary, it’s also handy to have both your router and modem (assuming they’re separate devices) connected to the same smart plug via a power strip. This allows you to power cycle both with a single trigger, and means you only need to sacrifice a single smart plug. Alternatively, you can plug both devices into separate plugs and make sure you target both with your automation.

With your smart plugs paired, correctly labeled, and attached to your network equipment, it’s time to set everything up.

Home Assistant Connect ZBT-2

Detecting when your internet connection goes down

There are a few ways you can detect when your internet connection is not online using Home Assistant. Some prefer to use the Healthchecks.io cron job monitoring service with its associated integration (available via the Home Assistant Community Store). Personally, I keep it a little simpler than that.

If you’re lucky enough to have a router that is detected by Home Assistant, you can use that. My TP-Link Archer AX72 router was automatically detected by the platform’s core UPnP/IGD integration and offers two handy “WAN status” triggers that I can use in automations.

For the purpose of this guide, I ignored that in favor of a simple ping operation that should work with pretty much any router. As long as your Home Assistant server is connected to the internet, you can use this to detect your connection status. To be safe, we’ll use two separate ping requests to different servers to account for any remote outages.

To get started, head to Settings > Devices & services and on the “Integrations” tab, click “+ Add Integration” at the bottom of the screen. Search for the “Ping (ICMP)” integration and add it. When prompted, enter 8.8.8.8 as the host, then finish up.

Finally, head back to the Ping (ICMP) integration and click “Add service,” then enter 1.1.1.1 as the host name, then “Submit” and “Skip and Finish.” We now have Home Assistant sending a ping every 30 seconds to Google and Cloudflare’s DNS servers.

Build your reboot automation

Finally, let’s build our automation. Click on Settings > Automations & scenes, then click “+ Create automation” at the bottom of the screen. Hit “Create new automation” when prompted, then click “+ Add trigger” and select the “Device” trigger.

You can choose to use your router’s WAN connection status here, where available. Otherwise, use the “Select a device” menu to choose one of the two DNS entries we created (let’s say 1.1.1.1). Under the “Trigger” drop-down, choose “1.1.1.1 disconnected” and then under “Duration” add the amount of time you’d like to elapse before everything is rebooted. I chose two minutes to account for any brief drop-outs.

Now, under the “And if” section, click “+ Add condition” and choose “Device” from the list of conditions. Now add the other DNS entry we created (in this instance, that’s 8.8.8.8). Change the condition to “8.8.8.8 is disconnected” and leave the duration blank. The automation now requires that both services be unreachable before anything will happen.

Lastly, it’s time to create a reboot sequence. Under “Then do,” click “+ Add action” and choose “Device” as an action. Now select the smart plug that’s connected to your router and modem (or add both separately, if that’s the route you’re taking) and make sure the “Action” is “Turn off” (for both, if necessary). You’ll need to use the “+ Add action” button again to add a second plug.

Now, click “+ Add action” and search for “delay” and add the “Wait for time to pass” action. Specify how long you’d like your equipment to remain off before the reboot sequence is triggered. I went for 10 seconds, but you might prefer to wait around 30.

Lastly, use the “+ Add action” button to add the same smart plug (or plugs) with a “Turn on” action (for both). This will restart your router.

Avoiding boot loops

A boot loop could occur where your Home Assistant server keeps power cycling for as long as things appear offline. In testing this automation (which depends on the Ping (ICMP) integration), this didn’t happen. My automation required that the servers come back online before it would trigger again.

If you find that you’re getting a boot loop, you could edit your automation and use the “+ Add action” button in the “Then do” section to disable the automation after you send the “Turn on” command at the end. You can then create another automation that turns the reboot automation back on when both ping requests report a “Connected” status.

If your router or modem is routinely causing problems, it’s probably time for a new one. I find that most consumer-grade routers tend to burn themselves out after a few years, especially routers provided by ISPs.

One of the few useful upgrades that Microsoft included in modern versions of Windows is Phone Link. It’s a built-in app that lets you sync an Android phone or tablet with a Windows PC and control it from there. The setup is pretty simple.

Start by looking up ‘Phone Link’ in the Start menu search and launch the Phone Link app. On the first launch, Phone Link will ask which type of device you want to sync. I’ll choose Android. Make sure you’ve installed Phone Link on the target Android device.

Phone Link might ask you to verify the connection with the PC’s password before proceeding. Next, Phone Link will present you with a QR code that you need with the Android device. Open the camera app or a dedicated scanner app to scan this QR code.

If everything goes well, you should see a welcome message and a checkbox to automatically launch the Phone Link app on startup. Uncheck this box if you don’t want the PC to automatically start a Phone Link connection on boot.

By default, Phone Link will show you the battery status, recent notifications, and media playback controls (if something is playing on your phone). You can grant additional permissions to view and send text messages, make and receive calls, and browse recent photos. You can also ping the phone from your PC which plays a sound on your phone for 20 seconds (helpful if you can’t find it)

The Phone Link app on your Android also has a dedicated button for sending files which should appear in the Downloads folder of your PC.

There is a cross-device copy-paste feature available in Phone Link, but it doesn’t work with every Android phone. Only Samsung and a handful of Chinese manufacturers support this feature. If you want a more reliable way to sync the clipboard between devices or if you want even more features, read on.

Try KDE Connect for better support and more features

I have Phone Link connected with my Windows PC, but I use KDE Connect the most. It’s available on other platforms too, and it supports clipboard sync for any Android phone or tablet. KDE Connect also offers more controls than Phone Link.

Start by installing KDE Connect from the Microsoft Store or grab the installation package from KDE’s website. Next, open Google Play Store and install KDE Connect on your phone. The package is also available on F-Droid.

Open KDE Connect on both devices. You should see a list of devices available on your network on both devices. On either the phone or the PC, select the target device and hit ‘Pair.’ You’ll see a pairing request on the target device, grant it, and you’re good to go.

The KDE Connect interface provides a bunch of tiles on the PC and the phone. It’s less visually appealing than Phone Link, but you can do more. For example, you can send text you’ve copied between the two devices with the ‘Send Clipboard’ tile. Also, KDE Connect automatically sends any text you copy to the phone for quick pasting. If you use Gboard, the copied text instantly shows up as a chip on the top row.

You can also send files back and forth with ease. There should be a ‘Send File’ button on both devices, which lets you share multiple files with your PC. You can also send files from the PC to the phone, but only one at a time.

KDE Connect also features a bunch of other utilities you might appreciate. You can use your phone as a presentation remote to control slides on a PC. The KDE Connect phone app can serve as a control for media playback. You can use your phone as a cursor and keyboard for the PC. You can also sync notifications between devices, sync contacts, and send or receive text messages from your PC.

• First, open the “Settings” app from the “Home” tab on the home screen
• Now go to “Security & Privacy”
• Select “Apps From Unknown Sources”

Find “Silk Browser” and then toggle on “Allow From This Source.” This is what will allow us to install an app from outside of the Amazon app store. With that out of the way, we can start downloading the Play Store files. There are four APK files we will need to get the Play Store up and running, and they’re specific to your Fire Tablet.

To find out which Amazon Fire Tablet model you have, go to Settings > Device Options > About Fire Tablet. You’ll see your “Device Model” name here. To see your Fire OS version, go to Settings > Device Options > System Updates.

With the device model in mind, we can download the appropriate files below. Simply copy and paste the links from the tables below into the Silk Browser on your Amazon Fire tablet. We’re just downloading the files at this point, don’t open them yet.

Google Account Manager

Google Services Framework

Google Play Services

Google Play Store

Install the Play Store

With all the downloaded APK files to your Amazon Fire Tablet, we can begin installing them one by one. Open the “Files” app from the home screen.

• Select “Downloads” from the side menu and switch to the list view for the files. You should see the four files we just downloaded.
• It’s important to install these APKs in a specific order. For each APK, follow this process: Tap the file > select “Continue” > tap the “Install” button. After it installs, tap “Done.” Don’t open the Play Store yet.

Install the files in this order (the file names on your device will be longer):

1. com.google.android.gsf.login
2. com.google.android.gsf
3. com.google.android.gms
4. com.android.vending

With all the APKs installed, it’s time to reboot the tablet. Hold down the power button and select “Restart.”

If you’re using the Fire 7 2022 (12th Gen) or a newer Fire tablet running FireOS 8, you may need to manually grant permissions before using the Play Store. Tap and hold the Play Store icon and select “App Info.”

From the App Info page, select “Permissions”.Select both “Phone” and “SMS” and switch them to “Allow.” You can now proceed with the rest of the tutorial.

After following the above instructions, open the Play Store from the home screen and sign in with your Google account. Once you’re signed in, you’ll have a functional Google Play Store, just like on any other Android device. Go forth and download Telegram, Chrome, Gmail, and any other app you can’t find in the Amazon Appstore.

In the Start menu, you’ll find a Recommended section featuring ads for different apps. The last time I checked, it was showing an ad for a password manager. Microsoft is probably aware that not every Windows user will appreciate this new addition to the Start menu, which is why there’s also an option to remove the Recommended section entirely.

To do this, press Win+i to open the Settings app. Choose “Personalization” from the left sidebar, then select “Start” on the right. Turn off the toggle next to “Show recommended files in Start, recent files in File Explorer, and items in Jump Lists.” After that, restart your system, and you’ll see that the Recommended section is gone from the Start menu.

Remove ads from File Explorer

There’s plenty of vacant space in Windows File Explorer, and perhaps that’s why Microsoft thought it would be the best place to display ads in Windows. The first time Microsoft tested this idea was in 2017, when a large OneDrive banner appeared at the top of File Explorer in Windows 10. However, for some reason, it was later removed from Windows.

Since 2022, when an X user named Florian Beaubois spotted ads in the Windows 11 File Explorer, Microsoft has been constantly pushing ads in its file manager.

If you want a completely ad-free File Explorer experience, you need to change one setting in the Folder Options. To do this, open File Explorer, click the ellipsis icon at the top, and choose “Options” from the menu that appears. Switch to the “View” tab, uncheck the “Show Sync Provider Notifications” box, and then click “Apply” and “OK” to save the changes.

Disable tips and suggestions from Windows

In addition to ads, Windows also shows a lot of suggestions and tips to improve your overall experience. While these tips can be helpful for someone who’s just getting started with Windows, if you’ve been using Windows for a while, you might find them as annoying as the ads. That’s why it’s best to turn them off completely for a clutter-free Windows experience.

To do this, open the Settings app, select “System” from the left sidebar, and click “Notifications” on the right. Under the Notifications From Apps and Other Senders section, click “Additional Settings.” Turn off the boxes next to “Get tips and suggestions when using Windows” and “Suggest ways to get the most out of Windows and finish setting up this device.”

After disabling these boxes, you will no longer see occasional pop-ups from Windows asking you to switch to Microsoft Edge, join Microsoft Rewards, or use any other Microsoft services.

Remove ads from the Lock Screen

I’ve long been an admirer of the Windows Spotlight feature. It dynamically changes lock screen images, ensuring that I’m not looking at the same picture every time. However, there’s one major drawback to using the Windows Spotlight feature.

You’ll see ads on your Windows lock screen if you have Windows Spotlight set as the display option. These ads often promote different games, and sometimes Microsoft also shows tips on various topics. The only way to remove ads from the lock screen is to configure Windows to show either a picture or a slideshow instead of Windows Spotlight.

To do this, open the Settings app, choose “Personalization” from the left sidebar, and click the “Lock Screen” option on the right. Click the drop-down icon next to “Personalize Your Lock Screen,” and then choose either “Picture” or “Slideshow.” If you select Picture, make sure to turn off the “Get fun facts, tips, tricks, and more on your lock screen” box.

Remove ads from the Settings app

The very place where you’ve been disabling ads till now ironically shows plenty of them itself. Yes, you guessed it right; I’m talking about the Settings app. You’ll often find ads for different Microsoft services appearing at the top of various pages in the Windows Settings app.

To remove ads from the Windows Settings app, open the Settings app, select “Privacy & Security” from the left sidebar, and click “Recommendations & Offers” on the right. Turn off the toggles next to these options: Personalized Offers, Improve Start and Search Results, Recommendations and Offers in Settings, and Advertising ID. Disabling the Advertising ID also ensures that third-party apps don’t show personalized ads based on your Windows activity.

Remove personalized ads from the Microsoft Store

When you search for an app on Microsoft Edge, you’ll often see recommendations and similar apps in the Microsoft Store. This happens because of the Microsoft Store’s personalized experiences setting, which allows the store to monitor your online activity to show relevant ads and app suggestions. Fortunately, you can disable this setting to stop the Microsoft Store from displaying personalized ads and recommendations.

To do this, open the Microsoft Store, tap the profile icon at the top, and choose “Store Settings” from the menu that appears. Turn off the toggle next to “Personalized Experiences.” Select “Confirm and Restart Store” from the pop-up window that appears.

Remove ads from Windows search

I always prefer to hide the Windows search from the taskbar. But if you’re someone who uses it regularly, you must have noticed that it shows a lot of ads.

Open Windows Search, and on the right panel, you’ll see ads for different games, a trending searches section, and a dedicated block highlighting what’s special about the current date. If you find these elements taking up too much space in Windows Search, you can completely remove them.

To do this, open Windows Search, click the ellipsis icon at the top, and choose “Search Settings” from the menu that appears. Turn off the toggle next to “Show Search Highlights.” Restart your computer, and you will no longer see ads in Windows Search.

Clear the Widgets panel

Although the widget panel doesn’t show any ads, it contains a long list of news stories. There are also plenty of other blocks with data related to the stock market and other content that may not be relevant to you. If you find all this distracting, you can either clear the widget feed or disable the widget panel altogether. However, I suggest doing the former, as the widget panel can be quite helpful in various situations.

To get rid of an irrelevant widget feed, click the cog icon at the top-right corner of the widget window and select “Show or Hide Feeds.” Then, disable the “Feeds” toggle.

Big datasets can be difficult to read, interpret, and analyze. Indeed, it can be difficult to track specific information and understand relationships between data points if you have to scroll through thousands of rows and columns, even if you use the Freeze Panes or Focus Cell tools.

However, converting a dataset into a PivotTable allows for simplified data exploration and analysis, instant insights, and quick comparisons. What’s more, PivotTables let you perform calculations without the need for complex formulas, and you can use slicers and timelines to only show the data you need at any given point.

You can create a PivotTable in Excel from an external data source, a table in your worksheet, or a pre-built data model. To access these options, in the Insert tab on the ribbon, click the “PivotTable” down arrow.

In my case, I want to analyze an Excel table (named T_Sales) I created in an Excel worksheet, and I want to do so in a new worksheet to keep my workbook tidy and organized. So, after selecting a cell in the table, I’ll click “From Table Or Range” in the PivotTable drop-down menu. Then, after making sure the correct range is selected, I’ll check “New Worksheet,” and click “OK”.

Opening the PivotTable Fields pane

The PivotTable Fields pane is where you decide what goes in the PivotTable. This pane usually opens by default on the right of your screen as soon as you initiate the PivotTable-building process.

The PivotTable Fields pane closes as soon as you click away from the PivotTable area.

If the PivotTable Fields pane doesn’t appear by default when you select a cell in the PivotTable area, or if it disappears at any point when you’re working on your PivotTable, click “Field List” in the PivotTable Analyze tab on the ribbon. You can also click the same button if you don’t want the PivotTable Fields pane to be displayed by default.

To move the PivotTable fields pane, hover your cursor over the top of the pane until it turns into a four-arrow move pointer, and click and drag the pane to a more convenient position on your screen as a floating window.

To turn it back into the default pane, click and drag it to the right of your screen until it clicks back into position.

Understanding the PivotTable Fields areas

When I first started using PivotTables in Microsoft Excel many years ago, I found the PivotTable Fields pane confusing, not only because the default layout is overly condensed, but also because what goes in the different fields isn’t immediately obvious.

By default, the fields (labeled A in the screenshot below) are stacked on top of the areas (labeled B in the screenshot below). If your source dataset is substantial, this layout makes everything appear overcrowded, especially if you’re working on a small screen.

• To fix this, click the “Tools” cog, and select “Fields Section And Areas Section Side-By-Side”
• This vertical layout makes the pane appear less cluttered and, thus, easier to use.

Before you go ahead and start building your PivotTable, take a few moments to understand what each area means:

• Rows: Fields inserted into the Rows area appear as row labels down the left-hand side of the PivotTable. In the screenshot below, the Product field being in the Rows area means the products in the source data are listed in column A.
• Columns: Fields added to the Columns area appear as column labels across the top of the PivotTable. In the example below, you can see countries in the column headers.
• Values: Fields placed into the Values area are usually variables with numeric values that form the main part of the PivotTable, with each value acting as an intersection between a column and a row. In the PivotTable below, each cell where a product and country intersect contains a profit value.
• Filters: Insert a field into the Filter area if you want to filter the entire PivotTable based on a given variable. In this case, I can filter the PivotTable by department.

Adding and removing fields to and from a PivotTable

There are two ways to add fields to an area in the PivotTable Field pane.

When you check a checkbox next to a field, Excel automatically moves it to one of the areas according to the type of data the field contains. Usually, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. Useful as this automation may be, fields sometimes go in the wrong areas. What’s more, when you use this method, there’s no way for Excel to know which field to add to the Filters area.

Alternatively, dragging and dropping a field into an area gives you more control over what goes where. If you want to change which area a field is added to, simply drag and drop it to the appropriate place. For example, to remove the Department filter, click and drag “Department” from the Filters area to the left of the PivotTable Fields pane.

To remove a field from a PivotTable area, either uncheck the corresponding checkbox in the fields section or drag and drop a field from the areas section to anywhere outside the PivotTable Fields pane.

Now that you have used the PivotTable Fields pane to build your PivotTable, use the PivotTable Analyze tab on the ribbon to manipulate and customize the data. For example, you can rename the PivotTable, group different fields, or insert slicers and timelines for dynamic filtering. On the other hand, you can change the PivotTable’s visual properties via the Design tab, such as adding subtotals, formatting the data into banded rows, or changing the colors of the headers.

The whole system is powered by Home Assistant, a free and open-source smart home platform. Home Assistant runs on dedicated hardware like an old laptop or a Raspberry Pi, works offline, and is compatible with a huge range of smart home gadgets, including the all-important sensors that make this system work.

In my case, there are only a few entry points to the house where an intruder is likely to enter. I’ve got doors at the front and the back of the house, each of which has separate screen doors between them and the outdoors. In addition, I have casement windows with relatively small individual panes of glass. The windows are made from hardwood, since the house itself is getting on for 100 years old.

The main method of forced entry would be to open these doors or windows. Smashing the glass would still require that the window be opened unless we’re being burgled by toddlers (and they’d probably never reach the sills anyway). Ergo, this gives me a simple solution to home security in the form of cheap door or window sensors. I’m also sticking one on the garage, which is separate from the main house.

The end goal is to trigger some sort of visual and audible alert when the sensors detect that a window or door has been opened at night or when nobody is in the house for a prolonged period. The benefit of this method is not having to rely on motion sensors, which can be easily triggered unintentionally by people getting up in the night or cats walking around the house.

You can use virtually any door or window sensor that’s compatible with Home Assistant to accomplish this. I picked up a few IKEA Parasoll sensors ($15) out of curiosity, before quickly realizing they’d be perfect for such a project. Like similar products, they use a magnetic sensor to detect a change whenever the door or window is opened.

They take a single AAA battery, and the range of motion is fairly generous, so both elements don’t need to be lined up perfectly. There’s a red LED that fires whenever the sensor detects a change, which makes installation rather easy. They use Zigbee and work with Home Assistant’s Zigbee Home Automation (ZHA) integration via the Home Assistant Connect ZBT-1 Zigbee radio.

What I’m triggering when the sensors detect a door opening is still a work in progress. For starters, I’ve got a HomePod that’s hooked up to Home Assistant through which I can play an alarm alert. My iPhone is linked to Home Assistant via the mobile app, so I can get notifications. I’ve got lots of connected lights, so I can trigger those too.

Writing the Automation

One of the biggest benefits of Home Assistant is its powerful automation system. This allows you to take several inputs, apply conditions to those inputs, and then fire off whatever actions you’d like to trigger. Being able to flesh out an automation by using building blocks is what gives this system the edge over platforms like Apple Home.

The first thing to do is define “When” triggers. You can do this from your main Home Assistant interface under Settings > Automations & scenes using the “Create automation” button. I used the “Add trigger” button to add “Device” triggers for all the sensors I wanted to use in the “open” position.

You can define a specific duration that the doors should be open if you want, but I left the trigger as instant. You can also add an “And Ii” condition, which qualifies the trigger. So, for example, you could use the “And If” condition to only trigger the automation between certain hours of the day, or based on some other condition of your choosing.

I left this blank, since I wanted to be able to arm and disarm the system manually. Our household has some erratic hours, which include regular early starts and occasional late nights. I didn’t want to chance false triggers at antisocial hours, but I can always change my mind at a later date.

Finally, there are the “Then do” actions. This is where you decide what should happen in the event of your sensors being triggered (read: a break-in). I decided to trigger a scene that turns on the lights, set my HomePod’s volume to 70%, and play the obnoxious alarm tone from Half-Life 2. A proper siren would give me more control and probably be a better deterrent, but this is all proof of concept right now.

You can play with this formula to your heart’s content. Add more smart speakers (if you have them), set a repeat condition so that your alarm tone repeats forever, and add mobile notifications to devices of your choosing.

Arming, Disarming, and Muting the System

To arm the system, all you need to do is turn on your automation under the Settings > Automations and scenes menu via the “Automations” tab. To disable it, just turn it off. To stop the alarm once it’s been triggered, turn off the automation (yes, this will kill whatever sound you’ve told it to play on your smart speakers).

How you interact with Home Assistant really depends on how you decide you want to arm and disarm your alarm, as does your daily routine. Arguably, the easiest way to do it is to tie it to a scene. For example, you probably have a “Goodnight” scene that turns off all the lights at night. You can add your alarm automation under “Entities” and set its state to “On” so that it comes on when you go to bed.

You also need to deactivate it in the morning. The easiest way to do this is to create a new automation that runs at a certain time of day. If you have a smart home control panel, you can add a handy toggle to that. If you use a voice assistant, you can use that. You could use an NFC tag on your way out, or anything else you can think of.

Personally, I’ve gone a slightly different route. I primarily use Apple Home to interact with Home Assistant, so I’ve exposed my Automation using the HomeKit Bridge integration as a switch within Apple Home. I can toggle the automation (and thus the alarm “system”) on and off by toggling this switch. I can include this in my Apple Home routines too, which means saying “Siri, Goodnight,” turns on the alarm and turns out the lights.

Doing this means that I can use Apple’s Shortcuts app to also disable the alarm automatically. I’ve got a failsafe set in Home Assistant so that the alarm turns itself off at 7 am to account for lie-ins, but I’ve also created a personal automation that runs a Shortcut to turn off the system when my iPhone alarm is turned off. You can add this to every family member’s iPhone, so that whenever they get up for an early start, the alarm will be deactivated before they’re even out of bed.

Having my security automation exposed in Apple Home also gives me one more super helpful control: being able to turn it off. Since Home Assistant kills the actions you’ve triggered when you turn the alarm off, I can hit a button to restore peace and quiet. I can also switch it on or off when I’m away from home.

Not only is this setup a work in progress (Half-Life 2 alarm noises, seriously), it’s also tailored to my own house. The windows in my place aren’t ideal for climbing through, even if you smash them first. Many homeowners cannot say the same. A vibration sensor stuck to larger panes of glass might be a good workaround, and some smart speakers can even recognize the sound and potentially give you a trigger.

Another thing to consider is electricity. Not everyone’s fuse box is locked up, and if would-be thieves cut power to the house first, then your system won’t work as intended (the same is true for power outages). This is also the case for many professional alarm systems, though suppliers often have pricey failsafes for such occasions. An uninterruptible power supply hooked up to your server and network equipment might be a good idea, since you’ll at least get mobile notifications (but no lights or sound).

This also isn’t a high-end system that automatically calls emergency services or a security company on your behalf. It’s a “better than nothing” hobby project that you can use to better understand what’s possible in Home Assistant.

The EOMONTH function in Microsoft Excel returns the last day of a month in the past, the current month, or a month in the future. Whether you need to calculate end-of-month due dates, deadlines, expiration dates, monthly revenues, or forecasts, the EOMONTH function helps speed up this process.

The EOMONTH function requires two arguments:

=EOMONTH(a,b)

where argument a is the start date (any day of any month in any year), and argument b is the number of months before or after the start date. After taking the start date and moving forward or backward the specified number of months, Excel returns the last date of the month it lands on.

Argument a can be a reference to a cell containing the start date, a start date entered using the DATE function, or a start date generated using another function, such as the TODAY function.

For argument b, a positive number represents the number of months after the start date, a negative number represents the number of months before the start date, and zero stays in the same month. You can hard-code this argument manually, or reference a cell containing the number of months you want to jump backward or forward:

Calculating future Month-End Dates

The most common use of the EOMONTH function in Excel is to return the last day of a month in the future. Imagine you’re the project manager of a construction company, and you’ve assigned projects to ten employees. Each project takes a certain length of time to complete, and completion deadlines are at the end of the month.

So, to calculate the end dates in column E, you need to use the EOMONTH function.

In cell E2, first type:

=EOMONTH(

Then, select the cell containing the start date and add a comma:

=EOMONTH([@[Start date]],

Finally, select the cell containing the number of months to jump forward, close the parentheses, and press Enter:

=EOMONTH([@[Start date]],[@Months])

There are two benefits to using cell references instead of hard-coding the date and number of months. First, the formula is correct according to the data on each row, and second, if you need to change the time period or start date of a given project, you can simply modify the values in columns B or D without editing the formula.

Calculating Past Month-End Dates

The EOMONTH function can also tell you the month-end date of a month that is a certain number of months before a start date. Confused? Let me show you a real-world example. In this example, you have a table of ten projects, and you want to work out the latest point at which your employees must start them to meet the deadline in column E.

Specifically, if a project takes five months, you want to return the month-end date of the month that is six months before the deadline, leaving some extra room for any problems that arise in the process. To do this, in cell D2, type the following formula and press Enter:

=EOMONTH([@Deadline],-[@Months]-1)

where, for each row, [@Deadline] references the deadline date in column E, -[@Months] jumps back the number of months in the Months column, -1 jumps back an extra month, and the EOMONTH function returns the final date of the resultant month.

So, for the project in row 2, the EOMONTH takes the deadline date of January 27, 2026, jumps back five months to August 2025, and returns the month-end date of the previous month—July 31, 2025. As a result, if Mark, the assignee, starts the project on July 31, 2025, he should complete the five-month project with a few days to spare, provided everything goes to plan.

Calculating Long-Term Past and Future Month-End Dates

The EOMONTH function in Excel is great for jumping a given number of months forward or backward from a start date, before returning the month-end date. However, what if you want to jump forward or backward a given number of years? Well, EOMONTH can take care of this, too.

Take this example, where you want to return the end date for each long-term project based on a start date and a year-based timescale. Specifically, the end date must be the end of a given month since this is when you like to review the progress of your projects.

The principle for jumping forward and backward a certain number of years is much the same as jumping forward and backward a certain number of months. However, the only difference is that you need to multiply the year values by 12, since the EOMONTH function only works with months, not years.

So, in cell E2, type:

=EOMONTH([@[Start date]],[@Years]*12)

Here’s what you get when you press Enter:

So, for Mark’s project in row 2, the EOMONTH formula used above takes the start date of January 4, 2025, jumps two years (24 months) forward to January 4, 2027, and returns the month-end date of January 31, 2027.

To jump back a given number of years, place a minus symbol (-) before the second argument. For example:

=EOMONTH([@[Start date]],-[@Years]*12)

Calculating the Month-End Date of the Current Month

While Excel’s EOMONTH function is mainly used to calculate the end date of a month in the past or future, it can also return the month-end date of the current month.

Suppose you’ve created an Excel workbook, where each month’s budget is on a separate worksheet. As well as using checkboxes and the SUMIF function to work out completed and upcoming expenditures, you want to use the EOMONTH function to calculate the number of days remaining in the month and, thus, the maximum amount you can spend each day.

First, in cell B2, you need to create a formula that subtracts today’s date from the month-end date. This will return the number of days left in the month. To do this, type:

=EOMONTH(TODAY(),0)-TODAY()

where

• EOMONTH(TODAY(),0) returns the serial number of the last date in the current month,
• TODAY() returns the serial number of the current date, and
• The minus symbol subtracts the latter from the former.

Now, you can calculate how much you can spend each day in cell B7:

=B6/B1

Generating Sequences of Month-End Dates

Combining functions in Excel can take your spreadsheet to the next level, and a good example of this is nesting the SEQUENCE function in an EOMONTH formula to return a list of month-end dates.

The SEQUENCE function, which acts as the second argument of the EOMONTH function, has four arguments:

=SEQUENCE(a,b,c,d)

where

• a is the number of rows to return,
• b is the number of columns to return,
• c is the first number of the sequence, and
• d is the increment between each value in the sequence.

Despite having four arguments, depending on what you’re looking to achieve, you probably don’t need to use them all. For example, here’s how the SEQUENCE function looks when combined with the EOMONTH function to produce an array of month-end dates for 2025:

=EOMONTH(DATE(2024,12,31),SEQUENCE(12))

The SEQUENCE function tells Excel to return a 12-row sequence, and the EOMONTH function defines the sequence as an array of month-end dates starting the month after December 31, 2024.

Let’s say you want to return the month-end dates for every second month—rather than every month—in 2025, starting January 31. In this case, type:

=EOMONTH(DATE(2024,12,31),SEQUENCE(6,,,2))

where the first argument of the SEQUENCE formula tells Excel to return six rows of results, and the fourth argument represents a two-month increment between each value in the sequence. Finally, if you wanted to return quarterly month-end dates, you would type:

=EOMONTH(DATE(2025,2,28),SEQUENCE(4,,,3))

where the start date in the EOMONTH part of the formula is any date in the month before the end of the first quarter, the first argument of the SEQUENCE part of the formula tells Excel that you want to return four rows of results, and the fourth argument tells Excel that you want to jump three months (one year quarter) between each result.

As you’ve seen in this guide, EOMONTH jumps forward or backward a given number of months and returns the last day of the resultant month. However, if you want to return the same day of the resultant month, use the EDATE function instead.

Libraries are collections of shared code. They’re common in Python, where they’re also called “modules,” but they’re also ubiquitous across other programming languages. A library defines functions that any programmer can use in their own programs, similar to how a public library offers anyone access to its materials.

The advantage of using libraries is that you can save time and effort by not having to code your own solutions. Libraries tend to be better tested and debugged than anything you could come up with. They will also let you do more than you could on your own.

There are numerous libraries in Python for tasks such as graphics, game development, and more. My favorite libraries tend to be for data analysis. Python is especially popular for statistics and data science due to the large number of libraries available for these tasks.

There are several ways to install Python libraries on your system. If you’re using a Linux distribution, there will often be Python libraries included in your distro’s package manager. For example, on Debian and Ubuntu systems, the libraries will often start with “python-” or “python3-“. This will install the library system-wide, so you’ll need to have administrative access.

If you don’t have root or administrative access on your system, there are still ways to install Python libraries locally.

You can use the pip tool to install Python libraries listed in the Python Package Index (PyPi).

For example, to install NumPy:

pip instlall numpy

You can install libraries on a per-project basis in virtual environments using the virtualenv utility. You can take the concepts of virtualenv further using Mamba, which happens to be my method of choice. Mamba is a tool popular among data scientists and analysts that allows you to create project-specific environments.

I already have a Mamba environment for statistical computing populated with several libraries that are popular for data analysis. I can activate it at the shell:

mamba activate stats

A lot of Python libraries will come with the language. This is a point of pride for the Python culture. The Python Standard Library has lots of modules for everything from interacting with the operating system to working with times and dates. This is why Python programmers like to say that the language has “batteries included.”

Importing a whole Python Library

To import a whole Python library in a script or in the interactive Python mode, use the import command.

For example, to import the NumPy module, use this command:

import numpy

With the library imported, you can access functions from that module. It’s like if you defined a bunch of functions. The functions are imported under their own namespace. This means that by default, any functions from the library are kept separate from the built-in commands in Python. I’ll show you some ways to change this, but the default behavior is better for most circumstances.

To get at these functions, you can call the module you just imported. It’s effectively an object, if you understand how object-oriented programming works. These functions are methods that are private to the object we just created by importing the library.

For example, to calculate the arithmetic mean or average of an array using NumPy, we’d type this:

numpy.mean(numbers)

This calls the mean function in NumPy.

You might think that typing “numpy” every time you want to access the functions is a lot, and you’re right. You can import libraries with other names as a shortcut. A common example you’ll see with NumPy is shortening the name to “np” in import statements:

import numpy as np

You can now refer to numpy as “np.” Let’s rewrite that earlier mean calculation:

np.mean(numbers)

Importing Part of a Library (Interactive Python Only!)

A lot of times, when you’re working in an interactive Python section, such as in the standard interactive Python interpreter or IPython, you’ll often want to import only one or two functions from a large library, especially if you want to use them repeatedly during a session. This is also easy to do in Python.

To import a single function from NumPy into the main namespace, you can use this construction:

from library import function

For example, to import the mean function from NumPy:

from numpy import mean

You can also import multiple functions by separating them with commas. For example, to import the mean and median functions from NumPy:

from numpy import mean, median

With these functions imported, you won’t have to preface them with “numpy” or “np.” You can just use them as if they were part of the default Python functions.

For example, to calculate the mean of an array of numbers

mean(numbers)

And the median:

median(numbers)

Importing functions by themselves is better-suited to interactive Python sessions because it saves on typing. You can do it in a script, but it’s not recommended. You might end up overwriting the namespace that’s used for a default Python function. This might cause a bug in your script. If you ask for help online in a forum or IRC chat, the other Pythonistas might get stumped. This is why you should avoid these kinds of imports in scripts as much as possible.

Creating and Importing Your Own Python Libraries

It’s also easy to import your own Python libraries. You can do it without having to be an expert in Python. All you need is a text editor and the ability to use a terminal. Python modules are just ultimately collections of functions defined in Python. You can just create a file ending in .py, the same way you were writing a script. If you’ve created your own Python functions, you can stick them in a file and import them, the same as with any other built-in or installed Python module.

To import a module, make sure that it has execute permissions. You can use the chmod command in Unix-like systems, including Linux and the macOS terminal:

chmod +x my_library.py

You can import the library using the methods described earlier. If you’re in the same directory as the module, you can just use import statements. For example, to import the library:

import my_library

If the library is in another directory, there are a couple of ways to change the search path. The first method is to use the PYTHONPATH environment variable. It’s a list of directories that Python will use to look for modules. You can modify this in your system shell either at the command line or in a startup file such as the .bashrc file in Bash. It’s a list of directories separated by a colon (:) character, similar to the PATH environment variable on Linux systems.

At the Linux shell, you can examine it with the echo command:

echo $PYTHONPATH

To change it, it’s best to append it.

export PYTHONPATH=”$PYTHONPATH:/path/to/my/modules”

This will ensure that any existing module search path is preserved before adding your own directory.

You can also modify the search path using the built-in sys module. Just import it in an interactive Python session:

import sys

Then examine it:

To add a directory to the search path, use the append method:

sys.path.append(‘/path/to/my/modules’)

Since there’s no real distinction between a Python script and a module or library, you can easily convert one to the other. It’s good programming style to break operations into smaller functions. You can also call them from another script or interactive section, but what if you have a script? You can just define the script’s operations in a “main” function.

This will check if the Python interpreter is executing the script as a script instead of importing. You can then put everything you want after this section to execute in the script, while leaving the functions to be possibly imported in another script or interactive session.

With the ability to use modules, you can tap into Python’s vast array of available modules. You’ll be able to do more than you thought you ever could by yourself. You’ll save a lot of time and effort over coding from scratch.