Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

5.7 Text Dates and Mixed Date Formats

By Last updated:

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

  1. Diagnose: =ISNUMBER(B2) – TRUE means a real date. Right-aligned cells are usually real dates.
  2. 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.
  3. Dots: Ctrl + H, replace . with -, then step 2.

  4. Formula for dd-mm-yyyy text (works whatever your PC's date setting):

    =DATE(RIGHT(B2,4), MID(B2,4,2), LEFT(B2,2))

  5. US mm/dd/yyyy text: =DATE(RIGHT(B3,4), LEFT(B3,2), MID(B3,4,2)).

  6. yyyymmdd number: =DATE(LEFT(B7,4), MID(B7,5,2), RIGHT(B7,2)).
  7. 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.
  8. Finally format the column dd-mm-yyyy and 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 – ha 3 April ki 4 March? Khup students hi chuk olkhat nahit, karan Excel ne dogha pan "date" mhanun ghetle. Dates mix asle tar source vichara kiwa dusrya rows madhun pattern olkha (13 peksha motha divas aala tar tya column madhe divas aahe). Cleaning nantar MIN/MAX date check kara – bhavishyatli date disli tar kahi tari chuklay.

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.