How to Convert Text Date to Numeric Format
Chapters & Timestamps
[00:01] Introduction: The Problem with Sorting Text Dates
[01:26] Checking Data Consistency with LEN Function
[03:50] Extracting the Year using RIGHT and VALUE
[08:05] Extracting the Month Name with LEFT
[09:40] Converting Month Names to Numbers using IFS
[14:16] Logic for Extracting Days using SEARCH & MID
[19:14] Handling Single vs. Double Digit Days with IFS
[25:12] Combining Date, Month, and Year using CONCAT
[27:04] Final Conversion to Date Value and Chronological Sorting
You can contact me on this :-
+923092177222
0
How to Convert Text Dates to Numeric Format
Struggling with messy date formats? In this video, I show you the easiest way to change dates from text (e.g., Mar 13, 2021) to a clean numeric format (13/03/2021). Whether you are working in Excel, Google Sheets, or just need to understand the logic behind date formatting.
In this tutorial, learn how to clean up messy text dates in Excel, extract the day, month, and year using formulas like MID and IFS, and combine them into a properly sortable numeric date format.
What you’ll learn:
How to quickly reformat dates without manual typing.
The difference between DD/MM/YYYY and MM/DD/YYYY.
Pro-tips for handling date data in spreadsheets.
This comprehensive tutorial explains how to convert text-based dates (e.g., "March 13, 2021") into a numeric format (e.g., "13/03/2021") that Excel can recognize as a number. This conversion is essential for sorting dates chronologically rather than alphabetically.
Video Analysis
The creator demonstrates a multi-step logic to handle varying text lengths (like "Jun 6" vs. "March 13"). The process involves:
Extracting Year: Using the RIGHT and VALUE functions.
Extracting Month: Using LEFT to get the month name, then a nested IFS formula to assign a number (1-12) to each month.
Extracting Day: Using SEARCH to find the comma position, combined with MID and IFS to extract the day regardless of whether it's one or two digits.
Combining & Formatting: Using CONCAT to merge the parts and Format Cells to apply the final date appearance.