Converting date information to m/d/yyyy

[Enough2Bdangerous]

I run my computer with regional settings that present date information in "m/d/yyyy" format. I use an application that exports date information to Excel in "dd/mm/yy hh:mm AM/AP" format. Is there an Excel formula that I can use to convert this date/time information to a format that is recognizable by my computer? Ideally in "m/d/yyyy hh:mm" or simply "m/d/yyyy"? Greatful for any assistance with this one.

[AliGW]

Welcome to the forum.

Does formatting the column not work for you? There are various ways that might work.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Enough2Bdangerous]

Thanks for the quick reply AliGW and thank you for the welcome. I use this forum a lot as a reference tool but only registered today because I couldn't find resolution to this specific issue.

The issue that I'm having is that my computer is not recognizing the "dd/mm/yy" date format because it is different from my computer's settings so the simple date conversion formulas won't work. For example, "13/03/2021" (Mar 13th) displays as "13/03/2021" while "12/03/2021" (March 12th) is recognized by excel as December 3rd in the same column of data. I've attached a sample file of the issue.

[AliGW]

In B2 copied down:

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(RIGHT(A2,4),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),SUBSTITUTE(LEFT(A2,2),"/","")))

[MrShorty]

I'm sure there's a formula, but, before I started messing with formulas, I would look at the data import step. All you've said is that an external programs exports information to Excel. What does that export look like (is it directly into Excel, via something like a text/csv file, other process)?

I'm also guessing that the dates in A2:A13 (3 Jan, 3 Feb, 3 Mar, etc.) are supposed to be 1 Mar, 2 Mar, etc., so that, even when Excel interprets it as a date, it is interpreting the wrong date.

At this stage, I would probably be aiming towards a process that brings the data as text into Excel (don't let Excel automatically convert from text to date). Once the text dates are in Excel, you can use the Text Import Wizard to convert the text to date. There's a step during the Text Import Wizard that specifically allows you to tell Excel that the dates are DMY dates, so Excel can import correctly. If the outside programming is exporting to a text/csv type of file, you can make the text import wizard part of the import process into Excel. Without knowing exactly how you are going from one program to Excel, my first thought is to find a process that uses text as the intermediate step. Then I can control how Excel imports the text.

[Enough2Bdangerous]

Amazing, thank you! You don't know how much of a headache you've just saved me!!

Can you share the formula to use to convert "dd/mm/yy hh:mm AM/PM" to "m/d/yyyy hh:mm"?

[AliGW]

If you share a workbook with the imported dates in that format, yes.

[Enough2Bdangerous]

Thank you. Please see attached. See column 'M' in yellow for new date format.

[Fluff13]

How about

Please Login or Register  to view this content.

[Enough2Bdangerous]

Thanks Fluff13. This formula works for the first 12 days of the month which Excel recognizes as dates but not for the 13th onward. Interesting formula though. A few functions in there that I have not come across before.

[Fluff13]

What do you get for M14 downwards?

[Enough2Bdangerous]

The formula output produces the same result as the original data.

[Fluff13]

Hadn't noticed your locale, which is why it doesn't work. Will the dates always be for year 2000+

[Enough2Bdangerous]

Yes, definitely nothing from pre-2000.

[Fluff13]

As I'm in England I cannot test this, but does this convert M14 into a date

Please Login or Register  to view this content.

[Enough2Bdangerous]

Yes, it works for M14 down but creates an error on M2 to M13. A combination of the two formulas could potentially work...

[Fluff13]

I just needed to check it would work for M14, try this for all the data

Please Login or Register  to view this content.

[Enough2Bdangerous]

That's the one. Thanks for your help with this one Fluff13.

[AliGW]

Thanks for finishing this off! I had to go offline.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who helped.

[Fluff13]

Glad to help & thanks for the feedback.

[Enough2Bdangerous]

Thanks guys! This is a great forum! I consider myself a very proficient Excel user but am humbled by other's capacities as well. Thank you!!