TOPIC: MICROSOFT WORD
Practical Excel skills for dates, locked files and text splitting
10th March 2026
Excel has a reputation for simplicity that does not always survive contact with real working life. Project schedules need to respect weekends and public holidays, files arrive locked at precisely the moment edits are most urgent, and data lands in cells formatted in ways that resist immediate analysis. Three everyday tasks illustrate this gap well: calculating future or past dates that exclude non-working days, removing or working around read-only restrictions when genuine edits are required, and splitting text strings cleanly at the first or Nth delimiter. The guidance below draws on established Excel features, keeping the emphasis firmly on what works in practice.
Working with Business Calendars
The WORKDAY Function
Working with dates that observe a business calendar is a frequent requirement in scheduling, logistics and reporting. Excel's WORKDAY function is designed for this purpose and returns a date that is a given number of working days from a start date, excluding Saturdays and Sundays by default. At its simplest, a formula such as =WORKDAY(A1,5) moves forward five working days from the date in A1. The function can also respect a list of holidays so that results avoid specific non-working dates as well as weekends, as in =WORKDAY(A1,5,C1:C3), which skips any matching dates in C1:C3 when calculating the result.
A concrete example shows how this behaves with a real date. Using named ranges for readability, with start referring to cell B5, days to B8, and holidays to B11:B13, the formula =WORKDAY(start,days) returns the next working day five days after 23rd December 2024. With weekends excluded, but no holidays provided, the result is Monday 30th December 2024. When holidays are supplied with =WORKDAY(start,days,holidays), the function also avoids the listed dates in B11:B13 and produces Thursday 2nd January 2025. In all cases, the weekend definition is Saturday and Sunday, and the holidays must be stored as valid Excel dates to be recognised correctly.
Visualising the Path to the Result
It often helps to see the individual dates that WORKDAY steps through when reaching its answer. A compact way to achieve this is to generate a short run of consecutive dates from the start date and display them alongside abbreviated day names. Using =SEQUENCE(13,1,start) in cell D5 creates thirteen dates beginning with the date held in the named range start because Excel dates are serial numbers that increment by one per day. Formatting these cells with the custom number format ddd, dd-mmm-yy shows an abbreviated weekday alongside the date, making it straightforward to spot weekends at a glance.
Conditional formatting can then shade non-working days directly within this generated block. Because WORKDAY does not evaluate a date when zero is supplied for the argument called days, a small workaround helps determine whether a given date is itself a working day. In column D, a rule based on the formula =WORKDAY(D5-1,1)<>D5 asks WORKDAY for the next working day after the previous day; if the answer does not equal the date in D5, then D5 is not a working day and can be shaded grey. A similar rule for column E, =WORKDAY(E5-1,1,holidays)<>E5, incorporates the named holiday range and produces additional shading where dates overlap with the supplied holiday list as well as weekends.
Highlighting calculated end dates ties the visualisation together. If the main results appear in G5 and G6, cells in column D can be highlighted when equal to $G$5 using a rule such as =D5=$G$5, and cells in column E can be highlighted when equal to $G$6 using =E5=$G$6. If preferred, the formatting rules can be defined without relying on G5 and G6 by embedding the WORKDAY calls directly in the comparisons, as in =D5=WORKDAY(start,days) and =E5=WORKDAY(start,days,holidays). In either arrangement, there are four conditional formatting rules in play across the grid: two to shade non-working days and two to pick out the final dates.
Handling Non-Standard Working Weeks
Work patterns do not always match the standard five-day week. Where a schedule follows a different rhythm, such as a four-day or six-day working week, switching to the WORKDAY.INTL function is the appropriate step. It follows the same principle as WORKDAY, returning a date a set number of business days from a start date while optionally excluding holidays, but it accepts a custom definition of which weekdays count as working days. This flexibility allows organisations that operate alternative rosters to generate accurate due dates without resorting to manual adjustments or complex helper columns.
Managing Read-Only Excel Files
There are several reasons why a workbook might be set to read-only: preventing accidental erasure of data, or ensuring a file remains unchanged as it passes between parties. Each form of protection serves a different purpose and has a distinct method for disabling it. ExcelRibbon.Tips.Net is a useful ongoing reference for these and many other workbook and security scenarios across Excel 2007 and later versions.
Read-Only Recommended
The lightest touch is the Read-Only Recommended setting. When a workbook carries this flag, Excel prompts on opening with a dialogue asking whether to open it as read-only. This method applies across all versions of Microsoft Excel from 2003 through to current releases. To remove the recommendation, open the workbook, use File > Save As and choose Browse to open the Save As window, then select Tools in the lower right of the dialogue, pick General Options, clear the Read-Only Recommended checkbox and click OK before saving. The next time the file opens, the prompt does not appear.
Marked as Final
A firmer signal is applied when a workbook is Marked as Final. In this state, commands, typing and proofing marks are all disabled, and Excel displays a Marked as Final notification bar at the top of the worksheet. To turn this off when editing is required, click Edit Anyway on the notification bar. This removes the read-only state for the current copy and allows modifications to proceed. The flag is more about signalling completion than enforcing security, so the application provides a clear override directly within the interface.
Password to Modify
Password protection introduces a stronger barrier. When a workbook has a Password to Modify set, a dialogue appears on opening that invites the password or offers the option to open the file as read-only; without the password, the file cannot be modified directly. A pragmatic path when only a working copy is needed is to open the original in read-only mode, then use File > Save As with Browse, select Tools > General Options, clear the Password to Modify field and confirm with OK before saving under a new name. Opening the newly saved file allows edits because it no longer carries a modification password. Using a third-party utility to crack a password on someone else's file is inadvisable and potentially inappropriate, so the better route is to request an editable version from whoever sent the document.
Operating System-Level Restrictions on a Mac
Occasionally, the read-only state is imposed not by Excel but by the operating system, where the file has been locked so that only the owner can edit it. On a Mac, the fix is made outside Excel: locate the file in Finder, right-click it and choose Get Info, then clear the Locked checkbox before reopening the file in Excel. If the issue is one of permissions rather than a simple lock, the Get Info window also contains a Sharing and Permissions section at the bottom. This lists each user alongside a drop-down privilege set to either Read Only or Read and Write, and the file owner can adjust these entries to grant editing access to the relevant users.
Operating System-Level Restrictions on a PC
On a PC, the equivalent controls are found in File Explorer. Right-clicking the workbook and choosing Properties opens the General tab, where unchecking the Read-Only attribute and clicking OK is often sufficient to restore full access. If the restriction stems from security permissions rather than the file attribute, the Security tab lists the groups and usernames that have access along with their permission levels. Clicking Edit beneath that list allows the file owner to adjust access for individual entries, including granting the ability to modify the file where that is justified.
Splitting Text in Excel
LEFT, MID and RIGHT
Reshaping text is another everyday requirement in Excel, and the LEFT, MID and RIGHT functions provide predictable building blocks. LEFT extracts a specified number of characters from the start of a string, MID extracts from a given position in the middle, and RIGHT extracts from the end. For instance, =LEFT("test string",2) returns te, =MID("test string",6,3) returns str, and =RIGHT("test string",2) returns ng. When exact character counts are known in advance these functions can be applied directly, but real data often arrives with variable-length segments separated by spaces or other delimiters, so the position of the delimiter must first be discovered before extraction can take place.
Splitting at the First Delimiter
To split at the first occurrence of a delimiter such as a space, combining these extraction functions with FIND or SEARCH is effective. Both functions return the position of a substring within a string, with the key distinction that FIND is case-sensitive while SEARCH is not. Suppose cell A1 contains test string. To return everything to the left of the first space, use =LEFT(A1,FIND(" ",A1)-1). Here FIND returns 5 as the position of the space, subtracting 1 yields 4, and LEFT uses that figure to return test. To return the text to the right of the first space, the formula subtracts the space position from the total string length: =RIGHT(A1,LEN(A1)-FIND(" ",A1)). In this example, LEN(A1) is 11 and the FIND result is 5, so the expression evaluates to 6 and RIGHT returns string. The pattern generalises to other delimiters by replacing the space character with the required alternative.
Locating the Nth Delimiter
Locating the Nth occurrence of a delimiter takes an extra step because FIND and SEARCH identify only the first match after a given starting point. A common technique relies on SUBSTITUTE to mark the Nth occurrence with a unique character that does not appear elsewhere in the text, then uses FIND to locate it. Consider An example text string in A1 and a requirement to return everything up to the third space. Substituting the third space with a vertical bar creates a dependable marker: =SUBSTITUTE(A1," ","|",3) produces An example text|string. Finding the bar with =FIND("|",SUBSTITUTE(A1," ","|",3)) returns 16, the position of the marker. LEFT can then extract the part before that position by subtracting one: =LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",3))-1), which returns An example text. Combining the steps in this way makes the formula self-contained and avoids the need for helper cells.
These text approaches extend naturally to extracting the segment after the Nth delimiter by using MID or RIGHT, with similar position logic. Replacing LEFT with MID and adjusting the start index to the marker position plus one retrieves the portion that follows. The same idea works with second or fourth occurrences by changing the instance number inside SUBSTITUTE. When working with data sets that include inconsistent spacing or punctuation, it is worth verifying that the chosen marker character does not already appear in the source text, since the method depends on its uniqueness within each processed string.
Bringing These Techniques Together in Your Excel Workflows
These three strands combine to form a toolkit that handles a surprising range of everyday scenarios. WORKDAY and WORKDAY.INTL anchor date calculations to real-world calendars so that estimates and commitments respect weekends and public holidays, while the SEQUENCE-based visualisation grid can help colleagues understand how an end date is reached rather than simply accepting a single cell value. Managing read-only states allows teams to balance protection with flexibility, with the key being to identify which type of restriction applies before attempting to remove it. LEFT, MID and RIGHT, combined with FIND and SUBSTITUTE, turn messy text into consistent fields ready for further analysis.
Building an email summariser for Apple Mail using both OpenAI and Shortcuts
3rd November 2025
One thing that I am finding useful in Outlook is the ability to summarise emails using Copilot, especially for those that I do not need to read in full. While Apple Mail does have something similar, I find it to be very terse in comparison. Thus, I started to wonder about just that by using the OpenAI API and the Apple Shortcuts app. All that follows applies to macOS Sequoia, though the Tahoe version is with us too.
Prerequisite
While you can have the required OpenAI API key declared within the Shortcut, that is a poor practice from a security point of view. Thus, you will need this to be stored in the macOS keychain, which can be accomplished within a Terminal session and issuing a command like the following:
security add-generic-password -a openai -s openai_api_key -w [API Key]
In the command above, you need to add the actual API key before executing it to ensure that it is available to the steps that follow. To check that all is in order, issue the following command to see the API key again:
security find-generic-password -a openai -s openai_api_key -w
This process also allows you to rotate credentials without editing the workflow, allowing for a change of API keys should that ever be needed.
Building the Shortcut
With the API safely stored, we can move onto the actual steps involved in setting up the Email Summarisation Shortcut that we need.
Step 1: Collect Selected Email Messages
First, open the Shortcuts app and create a new Shortcut. Then, add a Run AppleScript action and that contains the following code:
tell application "Mail"
set selectedMessages to selection
set collectedText to ""
repeat with msg in selectedMessages
set msgSubject to subject of msg
set msgBody to content of msg
set collectedText to collectedText & "Subject: " & msgSubject & return & msgBody & return & return
end repeat
end tell
return collectedTextThis script loops through the selected Mail messages and combines their subjects and bodies into a single text block.
Step 2: Retrieve the API Key
Next, add a Run Shell Script action and paste this command:
security find-generic-password -a openai -s openai_api_key -w | tr -d 'n'This reads the API key from the keychain and strips any trailing newline characters that could break the authentication header, the first of several gotchas that took me a while to sort.
Step 3: Send the Request to GPT-5
The, add a Get Contents of URL action and configure it as follows:
URL: https://api.openai.com/v1/chat/completions
Method: POST
Headers:
- Authorization:
Bearer [Shell Script result] - Content-Type:
application/json
Request Body (JSON):
{
"model": "gpt-5",
"temperature": 1,
"messages": [
{
"role": "system",
"content": "Summarise the following email(s) clearly and concisely."
},
{
"role": "user",
"content": "[AppleScript result]"
}
]
}When this step is executed, it replaces [Shell Script result] with the output from Step 2, and [AppleScript result] with the output from Step 1. Here, GPT-5 only accepts a temperature value of 1 (a lower value would limit the variability in the output if it could be used), unlike other OpenAI models and what you may see documented elsewhere.
Step 4: Extract the Summary from the Response
The API returns a JSON response that you need to parse, an operation that differs according to the API; Anthropic Claude has a different structure, for example. To accomplish this for OpenAI's gateway, add these actions in sequence to replicate what is achieved using in Python by loading completion.choices[0].message.content:
- Get Dictionary from Input (converts the response to a dictionary)
- Get Dictionary Value for key "choices"
- Get Item from List (select item 1)
- Get Dictionary Value for key "message"
- Get Dictionary Value for key "content"
One all is done (and it took me a while to get that to happen because of the dictionary → list → dictionary → dictionary flow; figuring out that not everything in the nesting was a dictionary took some time), click the information button on this final action and rename it to Summary Text. This makes it easier to reference in later steps.
Step 5: Display the Summary
Add a Show action and select the Summary Text variable. This shows the generated summary in a window with Close and Share buttons. The latter allows you to send to output to applications like Notes or OneNote, but not to Pages or Word. In macOS Sequoia, the list is rather locked down, which means that you cannot extend it beyond the available options. In use or during setup testing, beware of losing the open summary window behind others if you move to another app because it is tricky to get back to without using the CTRL + UP keyboard shortcut to display all open windows at once.
Step 6: Copy to Clipboard
Given the aforementioned restrictions, there is a lot to be said for adding a Copy to Clipboard action with the Summary Text variable as input. This allows you to paste the summary immediately into other apps beyond those available using the Share facility.
Step 7: Return Focus to Mail
After all these, add another Run AppleScript action with this single line:
tell application "Mail" to activateThis brings the Mail app back to the front, which is particularly useful when you trigger the Shortcut via a keyboard shortcut or if you move to another app window.
Step 8: Make the New Shortcut Available for Use
Lastly, click the information button at the top of your Shortcut screen. One useful option that can be activated is the Pin in Menu Bar one, which adds a menu to the top bar with an entry for the new Email Summary Shortcut in there. Ticking the box for the Use as Quick Action option allows you to set a keyboard shortcut. Until, the menu bar option appealed to me, that did have its uses. You just have to ensure that what you select does not override any combination that is in use already. Handily, I also found icons for my Shortcuts in Launchpad as well, which means that they also could be added to the Dock, something that I also briefly did.
Using the Shortcut
After expending the effort needed to set it up, using the new email summariser is straightforward. In Apple Mail, select one or more messages that you want to summarise; there is no need to select and copy the contained textual content because the Shortcut does that for you. Using the previously assigned keyboard combination, menu or Launchpad icon then triggers the summarisation processing. Thus, a window appears moments later displaying the generated summary while the same text is copied to your clipboard, ready to paste anywhere you need it to go. When you dismiss the pop-up window, the Mail app then automatically comes back into focus again.
Shrinking title bar search box in Microsoft Office 365 applications
6th December 2019
It might be a new development, but I only recently spotted the presence of a search box in the titles of both Microsoft Word and Microsoft Excel that I have as part of an Office 365 subscription. Though handy for searching file contents and checking on spelling and grammar, I also realised that the boxes take up quite a bit of space and decided to see if hiding them was possible.
In the event, I found that they could be shrunk from a box to an icon that expanded to pop up a box when you clicked on them. Since I did not need the box to be on view all the time, that outcome was sufficient for my designs, though it may not satisfy others who want to hide this functionality completely.
To get it, it was a matter of going to File > Options and putting a tick in the box next to the Collapse the Microsoft Search box by default entry in the General tab before clicking on the OK button. Doing that freed up some title bar space as desired, and searching is only a button press away.
Losing formatting with Windows copying and pasting
25th July 2008
Copy and pasting between Windows programs can cause unwanted formatting to be carried over. Copying text from Internet Explorer into Outlook is one example of this that I see a lot, and Word to Word does it too. A trick that I picked up for avoiding this copying of formatting comes from a while back: copying into Notepad and pasting from there. Doing the copy/paste shuffle in that way strips off the formatting baggage and allows the default formatting for the particular destination to be applied. There may be other and slicker ways to do this, but what I have described works for me.
Office 2007 on test…
23rd January 2007
With its imminent launch and having had a quick at one of its beta releases, I decided to give Office 2007 a longer look after it reached its final guise. This is courtesy of the demonstration version that can be downloaded from Microsoft’s website; I snagged Office Standard which contains Word, Excel, PowerPoint and Outlook. Very generously, the trial version that I am using gives me until the end of March to come to my final decision.
And what are my impressions? Outlook, the application from the suite that I most use, has changed dramatically since Outlook 2002, the version that I have been using. Unless you open up an email in full screen mode, the ribbon interface so prevalent in other members of the Office family doesn’t make much of an appearance here. The three-paned interface taken forward from Outlook 2003 is easy to get around. I especially like the ability to collapse/expand a list of emails from a particular sender: it really cuts down on clutter. The ZoneAlarm anti-spam plug-in on my system was accepted without any complaint, as were all of my PST files. One thing that needed redoing was the IMAP connection to my FastMail webmail account, but that was driven more by Outlook warning messages than by necessity from a user experience point of view. Though I have still to get my Hotmail account going, I lost that connection when still using Outlook 2002, and after I upgraded to IE7.
What do I make of the ribbon interface? As I have said above, Outlook is not pervaded by the new interface paradigm until you open up an email. Nevertheless, I have had a brief encounter with Word 2007 and am convinced that the new interface works well. It didn’t take me long to find my way around at all. In fact, I think that they have done an impressive job with the new main menu triggered by the Office Button (as Microsoft call it) and got all sorts of things in there; the list includes Word options, expanded options for saving files (including the new DOCX file format, of course, but the doc format has not been discarded either) and a publishing capability that includes popular blogs (WordPress.com, for instance) together with document management servers. Additionally, the new zoom control on the bottom right-hand corner is much nicer than the old dropdown menu. As regards the “ribbon”, this is an extension of the tabbed interfaces seen in other applications like Adobe HomeSite and Adobe Dreamweaver, the difference being that the tabs are the only place where any function is found because there is no menu back up. There is an Add-ins tab that captures plug-ins to things like Adobe Distiller for PDF creation. Macromedia in its pre-Adobe days offered FlashPaper for doing the same thing, and this seems to function without a hitch in Word 2007. Right-clicking on any word in your document not only gives you suggested corrections to misspellings but also synonyms (no more Shift-F7 for the thesaurus, though it is still there is you need it) and enhanced on-the-spot formatting options. A miniature formatting menu even appears beside the expected context menu; I must admit that I found that a little annoying at the beginning, but I suppose that I will learn to get used to it.
My use of Outlook and Word will continue, the latter’s blogging feature is very nice, but I haven’t had reason to look at Excel or PowerPoint in detail thus far. From what I have seen, the ribbon interface pervades in those applications too. Even so, my impressions of the latest Office are very favourable. The interface overhaul may be radical, yet it does work. Though their changing the file formats is a more subtle change, it does mean that users of previous Office versions will need the converter tool in order for document sharing to continue. Office 97 was the last time when we had to cope with that, and it didn’t seem to cause the world to grind to a halt.
Will I upgrade? I have to say that it is very likely given what is available in Office Home and Student edition. While that version misses out on having Outlook, the prices mean that even buying Outlook standalone to compliment what it offers remains a sensible financial option. Having a look at the retail prices on dabs.com confirms the point:
Office Home and Student Edition: £94.61
Office Standard Edition: £285.50
Office Standard Edition Upgrade: £175.96
Outlook 2007: £77.98
Having full version software for the price of an upgrade appeals to me, and it is likely to be the route that I take, if I replace the Office XP Standard Edition installation that has been my mainstay over the last few years. Having been on a Windows 95 > Windows 98 > Windows 98 SE > Windows ME upgrade treadmill and endured the hell raised when reinstallation becomes unavoidable, the full product approach to getting the latest software appeals to me over the upgrade pathway. In fact, I bought Windows XP Professional as the full product to start afresh after moving on from Windows 9x.