5.7 Text Dates and Mixed Date Formats
Before
| Order ID | Order Date (raw) | What Excel sees |
|---|---|---|
| BLK-3301 | 14-03-2026 | Text (left-aligned) |
| BLK-3302 | 03/14/2026 | Text (US order) |
| BLK-3303 | 2026-03-15 | Date |
| BLK-3304 | 15.03.2026 | Text |
| BLK-3305 | 16 Mar 2026 | Date or text (depends on settings) |
| BLK-3306 | 20260317 | Number |
After
| Order ID | Order Date |
|---|---|
| BLK-3301 | 14-03-2026 |
| BLK-3302 | 14-03-2026 |
| BLK-3303 | 15-03-2026 |
| BLK-3304 | 15-03-2026 |
| BLK-3305 | 16-03-2026 |
| BLK-3306 | 17-03-2026 |
Steps in Excel
- Diagnose:
=ISNUMBER(B2)– TRUE means a real date. Right-aligned cells are usually real dates. - One consistent text format (all dd-mm-yyyy): select the column › Data › Data Tools › Text to Columns › Delimited › Next › no delimiters › Next › Column data format: Date: DMY › Finish.
- Dots: Ctrl + H, replace
.with-, then step 2. -
Formula for dd-mm-yyyy text (works whatever your PC's date setting):
=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2)) -
US mm/dd/yyyy text:
=DATE(RIGHT(B3,4), LEFT(B3,2), MID(B3,4,2)). - yyyymmdd number:
=DATE(LEFT(B7,4), MID(B7,5,2), RIGHT(B7,2)). - DATEVALUE converts text in a format your system understands:
=DATEVALUE("16 Mar 2026"). Its result depends on regional settings – so for mixed files prefer the DATE(…) formulas. - Finally format the column
dd-mm-yyyyand check=MIN()and=MAX()of the dates look sensible.
For a column with mixed formats, first add a helper column that identifies the pattern (e.g. =IF(ISNUMBER(B2),"date",IF(ISNUMBER(FIND("/",B2)),"US",IF(LEN(B2)=8,"yyyymmdd","dmy")))) and apply the right formula per pattern.
Ravindra Bagale's Tip
03/04/2026 – is it 3 April or 4 March? Many students don't spot this mistake, because Excel accepted both as a "date". If the dates are mixed, ask the source or recognise the pattern from other rows (if a value bigger than 13 appears, that part is the day). After cleaning, check the MIN/MAX date – if you see a future date, something has gone wrong.
Ravindra Bagale's Tip – मराठी
03/04/2026 – हा 3 April की 4 March? बरेच students ही चूक ओळखत नाहीत, कारण Excel ने दोन्ही "date" म्हणूनच घेतले. Dates mix असतील तर source ला विचारा किंवा इतर rows मधून pattern ओळखा (13 पेक्षा मोठा आकडा आला तर त्या भागात दिवस आहे). Cleaning नंतर MIN/MAX date check करा – भविष्यातली date दिसली तर काहीतरी चुकलंय.
Ravindra Bagale's Tip – हिंदी
03/04/2026 – यह 3 April है या 4 March? बहुत से students यह गलती पकड़ नहीं पाते, क्योंकि Excel ने दोनों को "date" मान लिया. Dates mixed हों तो source से पूछो या दूसरी rows से pattern पहचानो (13 से बड़ा नंबर आए तो उस हिस्से में दिन है). Cleaning के बाद MIN/MAX date check करो – भविष्य की date दिखे तो कुछ गड़बड़ है.
Practice task
Convert a column containing "14-03-2026", "03/15/2026", "16.03.2026" and "20260317" into real dates shown as dd-mm-yyyy. Prove it with =ISNUMBER() and by calculating the weekday of each.