Date format conversion nightmare

**toulouse330** · 01-16-2021, 01:07 PM

Hi all,

I routinely work with multiple reports containing dates which often are in differing format. Some in General, some in Date and some in Text. Most often, my habit is to copy the date information to Notepad, format the column in the destination file to Text, then copy from Notepad back into Excel. I say this only to let you know I have a fair amount of experience with figuring out how to make the values match for computational purposes.

Recently, however, I have been working with reports in which the dates are presented with leading zeroes for the single digit day and month - e.g. March 3, 2020 displays as 03/03/2020. These are in General format. I am not wanting to alter these as they are successfully interfacing with many other spreadsheets I work with. My issue is that I have a key report I get with dates in Date format and for the life of me, I cannot get those converted to the leading zero General format. I have tried every trick I know and no joy.

Thanks in advance

**AliGW** · 01-16-2021, 01:13 PM

Not quite sure what to say. The two dates in your attachment are not the same - one is 9 June and the other 6 September.

What I can tell you is this: the US’ insistence on doing dates differently to the rest of the world causes us all a whole world of pain!

And that’s the issue, I believe: the dates you are importing are in dd/mm/yyyy format.

**toulouse330** · 01-16-2021, 01:23 PM

Hi Ali,

Thanks for the quick response.

Back in a former life, I bought and sold commercial jet aircraft and I always used an alpha numeric format to avoid confusion - e.g. 03MAR2020. Leave it to us here in the colonies to resist conforming to the world order.

That said, in my lens, they are the same date. How can I take the 6/9/2013 value and convert it to 06/09/2013 (June 9, 2013). I'm willing to parse the values out (Data Text To Column), reformat, convert, whatever, but I need to get to the latter format.

**AliGW** · 01-16-2021, 01:33 PM

OK - let me try again.

The date in B7 is not a date. It’s text. If it were a date, then it would show in B7 as a 5-digit number. If you change C7 to general format, it shows 41434, which is the serial number for 9 June 2013. 41523 would be the serial number if that date were 6 September 2013.

In short, you will need to extract the date parts using LEFT, MID and RIGHT functions and then convert these into a real date before you will be able to custom date format the cells they are in.

**AliGW** · 01-16-2021, 01:36 PM

Similar issue in this thread: https://www.excelforum.com/excel-gen...ion-issue.html

**toulouse330** · 01-16-2021, 02:00 PM

I figured it out.

**AliGW** · 01-16-2021, 02:05 PM

That will work. It’s the sort of approach I was suggesting at the end of post #4.

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 those who helped.