Function Problems

Excel Formula to Convert Multiple Dates to Weekday Names

Olivia Miller Oct 12, 2026 869 views

Question details

The user wants to convert a single cell containing a comma-separated list of dates into their corresponding abbreviated weekday names.

How to Convert Multiple Dates to Weekday Names in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Data formatting and text extraction where multiple dates (like 12/5, 12/6) are stored as a text string in a single cell, and the goal is to extract their weekday equivalents.
Observed behavior: Transforming an input string like "12/5, 12/6, 12/10" into an output string like "Tue, Wed, Sun".

Before you start

Ensure your spreadsheet software supports dynamic array functions. The solution relies on TEXTSPLIT and TEXTJOIN, which are available in Microsoft 365, Excel 2021, and recent versions of WPS Office.

Solution 1Recommended

Use TEXTJOIN and TEXTSPLIT Array Formula

This method efficiently splits the text into an array, converts the text strings into serial dates, extracts the weekday names, and merges them back into a single string.

This approach nests four different functions together to process the text array without needing helper columns. The TEXTSPLIT function breaks the comma-separated string into individual items. DATEVALUE ensures they are recognized as actual dates. The TEXT function formats them into weekday abbreviations, and finally, TEXTJOIN brings the processed array back together.

Select the destination cell

Click on the empty cell where you want the resulting weekday names to appear.

Enter the nested formula

Assuming your original dates are in cell A1, type the following formula: =TEXTJOIN(", ",TRUE,TEXT(DATEVALUE(TEXTSPLIT(A1,", ")),"ddd"))

Execute the formula

Press the Enter key to calculate the formula. The cell will now display the abbreviated weekday names separated by commas.

Use TEXTJOIN and TEXTSPLIT Array Formula

Customize Formatting: If you prefer the full weekday names (e.g., Tuesday instead of Tue), change the "ddd" format code in the formula to "dddd".

Advanced Spreadsheet Tool

Easily Handle Complex Array Formulas with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern dynamic array functions like TEXTJOIN and TEXTSPLIT. You can easily process complex text string conversions, date parsing, and data cleaning tasks all in one place.

1. Download WPS Office: Install WPS Office for free and open the WPS Spreadsheet application.
2. Enter Your Data: Type your comma-separated dates into a single cell, for example, cell A1.
3. Apply the Formula: Paste the TEXTJOIN and TEXTSPLIT formula into a new cell to instantly convert your dates to weekdays.

Fully compatible with Microsoft Excel (.xlsx) file formats and formulas.Includes all advanced array, text, and date functions for complex calculations.Lightweight architecture ensures fast calculations even with massive datasets.Familiar tab layout makes transitioning seamless without a learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

What if my version of Excel doesn't support TEXTSPLIT?

If you are using Excel 2019 or older, TEXTSPLIT is not available. You must first use the 'Text to Columns' feature under the Data tab to split the dates into separate columns. Once split, use the TEXT function on each individual cell, and then use CONCATENATE or the '&' operator to join the results back together.

Why is the formula returning a #VALUE! error?

The DATEVALUE function requires the date strings to match your system's regional date format. If your dates are in MM/DD format but your system expects DD/MM, the DATEVALUE function will fail to recognize them as valid dates, resulting in a #VALUE! error.

Can I use this formula to get the month name instead of the weekday?

Yes, the TEXT function dictates the output format. To extract the abbreviated month name (like Dec), simply change the "ddd" in the formula to "mmm". For the full month name (like December), change it to "mmmm".

Excel & Data Problems