Are you tired of sorting your data alphabetically instead of by the order of weekdays? Do you want to easily represent and interpret patterns in data over weekdays? Then keep reading! We have a unique approach that utilizes the TEXT, SEQUENCE, and XLOOKUP combo to convert weekday names into numbers in Google Sheets.
How to Convert Weekday Names to Numbers in Google Sheets
To convert weekday names to numbers, we will use the XLOOKUP function to look up the search keys (weekday names) in one array (lookup_range) and return the corresponding numbers from another array (result_range).
Syntax:
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
The first array will contain the weekday names, and the second will contain the corresponding numbers.
What makes this formula interesting is the way we generate these two arrays. We will use the TEXT and SEQUENCE combination for the first array and SEQUENCE for the second array.
Let’s delve into these tips and convert weekday names to numbers.
Steps
Step 1: Search Key
Start by identifying the search key, which can be a single weekday name or a range of weekday names. For example, let’s say the search key is “Wednesday” in cell A1.
Step 2: Generating the Lookup Range
To generate the lookup range, which contains the weekday names, you can use an array formula that utilizes the TEXT and SEQUENCE functions.
=ArrayFormula(TEXT(SEQUENCE(7), "DDDD"))This formula will autofill the weekday names from Sunday to Saturday in a column. Place it in cell C1.
Step 3: Generating the Result Range
The result range is simply a sequence of numbers from 1 to 7. You can use the following SEQUENCE function to generate it.
=SEQUENCE(7)Place this formula in cell D1.
Formula to Convert Weekday Names to Numbers:
To convert the weekday name in cell A1 to the corresponding weekday number, use the following formula in cell B1.
=ArrayFormula(XLOOKUP(A1, ArrayFormula(TEXT(SEQUENCE(7),"DDDD")), SEQUENCE(7)))But wait, let’s improve the formula by moving the ArrayFormula function to the beginning.
Final Formula:
=ArrayFormula(XLOOKUP(A1, TEXT(SEQUENCE(7),"DDDD"), SEQUENCE(7)))To convert a range of weekday names in column A (A1:A), replace the search key A1 with A1:A. That’s it!
Additional Tips
1. Managing Abbreviations of Weekday Names in the Search Key Column
When converting weekday names to numbers, it’s important to account for abbreviated weekday names. The previous formula isn’t configured to handle them. To handle abbreviated weekday names such as “Sun” for “Sunday,” modify the formula by replacing the text formatting style “DDDD” with “DDD”.
=ArrayFormula(XLOOKUP(A1:A, TEXT(SEQUENCE(7), "DDD"), SEQUENCE(7)))If your data includes both abbreviated and non-abbreviated forms of weekday names, you can use the following formula:
=ArrayFormula(XLOOKUP(A1:A, VSTACK(TEXT(SEQUENCE(7), "DDDD"), TEXT(SEQUENCE(7), "DDD")), VSTACK(SEQUENCE(7), SEQUENCE(7))))This formula handles both types of weekday names by stacking them appropriately.
2. Converting Weekday Names to Numbers with Custom WEEKDAY Types (e.g., 1 for Monday, 7 for Sunday)
By default, our formulas return numbers 1-7 for Sunday-Saturday. If you prefer 1-7 for Monday-Sunday, you can easily achieve that by modifying the SEQUENCE formula part in the lookup range.
Replace the current formula part SEQUENCE(7) with SEQUENCE(7, 1, 2).
=ArrayFormula(XLOOKUP(A1:A, TEXT(SEQUENCE(7, 1, 2), "DDDD"), SEQUENCE(7)))Replace “DDDD” with “DDD” based on whether you have non-abbreviated or abbreviated weekday names.
For an all-weather formula that handles both types of weekday names, use the following:
=ArrayFormula(XLOOKUP(A1:A, VSTACK(TEXT(SEQUENCE(7, 1, 2), "DDDD"), TEXT(SEQUENCE(7, 1, 2), "DDD")), VSTACK(SEQUENCE(7), SEQUENCE(7))))And that’s it! Now you can easily convert weekday names to numbers in Google Sheets.
We hope you found these tips helpful. If you have any suggestions or feedback, feel free to share them with us. And remember, for more exciting Google Sheets techniques and tricks, visit Crawlan.com!
