TOPIC: ORIGIN
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.