Mixed Date Formats (DD/MM/YY and MM/DD/YY) in same Column

AfuaImade · Aug 3, 2019

Hello,

- I have a spreadsheet with loads of rows
- In column C, I have dates with mixed formats (DD/MM/YY and MM/DD/YY)
- Also some of these dates are showing as text(i.e to the left of the cell
P.S The dates are mixed format, I dont need to change them all, which is what will happen. I'll just end up with the same problem reversed, if I use your suggestion. Thanks.

Any help to get them all to dd/mm/yy will be appreciated.

AfuaImade · Aug 5, 2019

Re: Mixed Date Formats in the same Column

https://docs.google.com/spreadsheets/d/1EwPxT7jTeOKlm5xNwILha6pVZpDDGbQYOErC-IruWcM/edit?usp=sharing

Thanks for the feedback.
I've posted an online link as you've requested.

Akuini said:
Sorry, I don’t quite understand your explanation.
Can you explain it again in more detail how you determine which dates are wrong?

You could upload your sample workbook (without sensitive data) somewhere (maybe to dropbox.com) then put the link here.

MARK858 · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Your dates are all in dd/mm/yyyy format + the date and if your windows regional dates are setup as dd/mm/yyyy then the strange dates will convert to real dates and time if you do....

Select Column C, click "Data" tab - click "Text To Columns" - select "Delimited" and click "Next", make sure "Other" is not checked and click "Next", select General and click "Finish".

MARK858 · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Typo in the previous post "dd/mm/yyyy format + the date" should have read as "dd/mm/yyyy format + the Time"

AfuaImade · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Hi Mark,

Thanks for your response and for viewing the sheet.
The dates on my laptop is mm/dd/yy. The problem I'm having is as below and it's two fold

Context: The data is for the last 3 months (Month 4, Month 5 and Month 6) - April, May, June

Problem 1: The dates in the first two rows (row 1621 and 1622) are recognized as numbers however based on contextual information those dates should be April dates not December dates.

Problem 2: The dates in bottom two rows (row 1623 and 1624) are recognised as text and should be recognised as dates.

This problem repeats itself through the dataset and makes it difficult to analyze the data.

I hope the problem is a bit clearer now.

Thanks for your help.

MARK858 said:
Typo in the previous post "dd/mm/yyyy format + the date" should have read as "dd/mm/yyyy format + the Time"

MARK858 · Aug 5, 2019

Re: Mixed Date Formats in the same Column

I hope the problem is a bit clearer now.

The problem was always clear (bar you didn't mention the time being there at the start), what you wanted to do about it and your computer set up wasn't.

If your windows regional date setting is mm/dd/yyyy on your laptop then you need to convert all the dates on the spreadsheet to mm/dd/yyyy not to dd/mm/yyyy as you have asked in the question.

I'll leave it for now to see if any of the posters with a mm/dd/yyyy setup post a solution as it is easier for them to test.

steve the fish · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Try this and see if it works:

=IF(ISNUMBER(C2),DATE(YEAR(C2),DAY(C2),MONTH(C2))+MOD(C2,1),DATE(MID(C2,7,4),LEFT(C2,2),MID(C2,4,2))+TIMEVALUE(MID(C2,13,99)))

AfuaImade · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Hello Steve,
Thanks for your response
I tried this
Here's the sheet https://docs.google.com/spreadsheet...ILha6pVZpDDGbQYOErC-IruWcM/edit#gid=176773890
But it didn't exactly work out.

First off the dates that were earlier showing as text are now being recognised as numbers, but returning dates in 2020 which is not correct.

Maybe you can help look into this?
Thanks.

AfuaImade · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Hello Mark,
Hope this isn't too elementary but as you advised, how do I convert all the dates on the spreadsheet to mm/dd/yy

AfuaImade · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Hi Mark,

So I've changed the date settings on my computer and that leaves me with one issue now.

- Converting the dates recognized as texts to dates.

steve the fish · Aug 5, 2019

Re: Mixed Date Formats in the same Column

Try it like this:

<bdo dir="ltr">=IF(ISNUMBER(C1623),DATE(YEAR(C1623),DAY(C1623),MONTH(C1623))+MOD(C1623,1),DATE(MID(C1623,7,4),MID(C1623,4,2),LEFT(C1623,2)))</bdo>

Mixed Date Formats (DD/MM/YY and MM/DD/YY) in same Column

AfuaImade

New Member

AfuaImade

New Member

Excel Facts

MARK858

MrExcel MVP

MARK858

MrExcel MVP

AfuaImade

New Member

MARK858

MrExcel MVP

steve the fish

Well-known Member

AfuaImade

New Member

AfuaImade

New Member

AfuaImade

New Member

steve the fish

Well-known Member

Similar threads

Forum statistics

Share this page

Mixed Date Formats (DD/MM/YY and MM/DD/YY) in same Column

New Member

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Well-known Member

New Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock