How to Extract the Month from Date and Time Text Values in Excel
Question details
The user needs to extract the month name or number from a text string containing a date, time, and timezone.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning and formatting imported data where date and time include timezone information, making it act as text instead of a recognizable date format.
- Observed behavior
- Excel treats values like '2020-09-01 12:02:54 +0300' as text. Directly applying date functions or formatting fails unless the text is parsed first.
Identify the exact structure of your text string. Ensure the date portion is consistently positioned at the beginning (e.g., the first 10 characters) across all your data cells.
Use DATEVALUE and LEFT to Extract Date and Format as Month
Extract the date portion of the text string and apply custom formatting to display only the month.
By using the LEFT function, you can isolate the first 10 characters (the date). The DATEVALUE function then converts this text into a readable Excel serial date number, which can be formatted as you wish.
Click on an empty cell where you want the month to appear.
Type =DATEVALUE(LEFT(A1,10)) to extract the first 10 characters and convert them into a serial date number.
Right-click the cell, select 'Format Cells', and go to the 'Custom' category. Type mmmm to display the full month name (e.g., September) or m for the month number.
Use TEXT Function to Convert to a Month Text String
Convert the extracted date directly into a text string representing the month, which is highly recommended for matching data in COUNTIFS.
Extract and Manage Dates Effortlessly with WPS Spreadsheet
WPS Spreadsheet provides powerful data extraction tools and full compatibility with Microsoft Excel formulas. Easily handle complex date, time, and timezone text strings using functions like DATEVALUE, LEFT, and TEXT without modifying your existing workflow.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the complex date and time strings.
- 2. Select Cell and Type Formula: Click on a blank cell and input =TEXT(DATEVALUE(LEFT(A1,10)),"MMMM") to isolate and convert the date into text.
- 3. Auto-Fill Data: Drag the small square at the bottom-right of the cell to fill the formula down your column instantly, processing hundreds of rows at once.
Frequently Asked Questions
Why does Excel treat my date and time as text?
When data is imported from other systems, APIs, or databases, it often contains extra characters like timezones (e.g., '+0300'). Excel cannot natively recognize this combination as a standard date format, so it defaults to treating the entire cell as a single text string.
Why is my COUNTIFS formula returning 0 when matching months?
This happens when there is a data type mismatch. If your criteria is a text string (like 'September') but the referenced range contains actual date serial values formatted to just display the month, COUNTIFS won't match them. Ensure both sides are either text or dates.
How do I extract just the month number instead of the name?
If you need an actual integer representing the month, use the formula =MONTH(DATEVALUE(LEFT(A1,10))). This will return a number from 1 to 12 based on the extracted date.
