5.7 Text Dates and Mixed Date Formats
Convert text dates with DATE, LEFT, MID and RIGHT with example — in 30 seconds
What: When a date arrives as text like "15/11/2026", you cut out the day, month and year and rebuild a real date with DATE.
Why: Text dates cannot be grouped by month or used in EOMONTH. Real dates can.
Tiny example: "15/11/2026" → RIGHT gives 2026, MID gives 11, LEFT gives 15 → a real date 15-11-2026.
Convert text dates with DATE, LEFT, MID and RIGHT with example — 30 सेकंदात
काय: Date "15/11/2026" असा text म्हणून आला की दिवस, महिना आणि वर्ष कापून DATE ने खरी date परत बनवायची.
का: Text dates महिन्यानुसार group करता येत नाहीत आणि EOMONTH मध्ये चालत नाहीत. खऱ्या dates चालतात.
छोटं उदाहरण: "15/11/2026" → RIGHT 2026 देतो, MID 11 देतो, LEFT 15 देतो → खरी date 15-11-2026.
Convert text dates with DATE, LEFT, MID and RIGHT with example — 30 सेकंड में
क्या: Date "15/11/2026" जैसे text में आए तो दिन, महीना और साल काटकर DATE से असली date फिर से बनाते हैं.
क्यों: Text dates को महीने से group नहीं कर सकते और EOMONTH में नहीं चलतीं. असली dates चलती हैं.
छोटा उदाहरण: "15/11/2026" → RIGHT 2026 देता है, MID 11 देता है, LEFT 15 देता है → असली date 15-11-2026.
First look at the data (before → after):
Expected result — Real dates in column C (format dd-mm-yyyy)
| B: Raw | C: Real date | =ISNUMBER(C2) |
|---|---|---|
| "02/11/2026" | 02-11-2026 | TRUE |
| "15/11/2026" | 15-11-2026 | TRUE |
| "30/11/2026" | 30-11-2026 | TRUE |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Vikram Rao) are made up for this lesson on text dates and mixed date formats.
Before table and formula as text
Before — Dates exported as text (dd/mm/yyyy)
| B: Raw | =ISNUMBER(B2) |
|---|---|
| "02/11/2026" | FALSE |
| "15/11/2026" | FALSE |
| "30/11/2026" | FALSE |
About this data: Demo table only — not real customer or company data. Built to practise text dates and mixed date formats.
Formula / click path
C2: =DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))
Do it step by step:
- Type
=ISNUMBER(B2)to confirm the date is text (FALSE). - In C2, type
=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2)). - Format column C as dd-mm-yyyy with Ctrl + 1.
- Copy down and check
=ISNUMBER(C2)now says TRUE. - For mm/dd/yyyy files, swap the MID and LEFT parts.
स्टेप बाय स्टेप करून बघा:
- Date text आहे का ते बघायला
=ISNUMBER(B2)type करा (FALSE). - C2 मध्ये
=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))type करा. - Ctrl + 1 ने column C ला dd-mm-yyyy format द्या.
- खाली copy करा आणि
=ISNUMBER(C2)आता TRUE दाखवतो का ते check करा. - mm/dd/yyyy files साठी MID आणि LEFT चे भाग अदलाबदल करा.
स्टेप बाय स्टेप करके देखें:
- Date text है या नहीं, यह देखने के लिए
=ISNUMBER(B2)type करें (FALSE). - C2 में
=DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))type करें. - Ctrl + 1 से column C को dd-mm-yyyy format दें.
- नीचे copy करें और check करें कि
=ISNUMBER(C2)अब TRUE दिखाता है. - mm/dd/yyyy files के लिए MID और LEFT वाले हिस्से आपस में बदल दें.
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 |
About this data: Made-up rows so you can copy the pattern for text dates and mixed date formats. Currencies may use ₹, £, € or $.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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.
Loading practice tasks...
- Is it a real date? (Easy): Is C2 a number (real date)? (TRUE or FALSE)
- Get the year (Easy): Take the year text from C2.
- Get the day (Easy): Take the day text from C3.
- Get the month (Easy): Take the month text from C3.
- Build the date (Indian style) (Medium): Make a real date from C2.
- Build the date (US style) (Medium): Make a real date from D3.
- Build the date (yyyymmdd) (Medium): Make a real date from E4.
- Month number of a trip (Medium): Which month (number) is the trip in C5?
- Days between two trips (Hard): How many days from trip B-01 (C2) to trip B-02 (C3)?
- Do two websites agree? (Hard): Is the C6 date equal to the D6 date? (TRUE or FALSE)
- Weekday of a trip (Hard): Which weekday is the trip in E2? (1 = Sunday, 7 = Saturday)
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
