• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

US Date format Convert Help

M

mrzoogle

Member
Hi Excel Gurus,


I am having trouble converting this 05/01/2013 date format to 01/05/2013.


Simply I am trying to convert US time format to UK time format, and I have tried quite a few formulae and no luck, it returns an error.


As the source of the data is from a csv file, I think it is converting these dates into text.


If you copy and paste this date 05/01/2013 in your excel file it'll be more clear.


Thanks for your time.


Kind Regards,


Z.
 
1) Select all your data, to a Text-to-Columns with "/" as the delimiter

2) In col D, create this formula:

=DATE(C2,A2,B2)

3) Copy col D, paste special - Values to wherever you want the data.


Note: This assumes your local regional settings are already in UK format. If not, I'd suggest just using a format of dd-mmm-yyyy, as it prevents any confusion.
 
Hi Luke,


Thanks for the solution, it works perfectly as usual :)


I was wondering are there any formula which does not require text to column?


Thanks for your time.


Kind Regards,


Z.
 
Hi, mrzoogle!

Try this:

a) cell formatted as text: FECHA(DERECHA(A1;4);IZQUIERDA(A1;2);EXTRAE(A1;4;2)) -----> in english: =DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

b) cell formatted as date: =FECHA(AÑO(A1);DIA(A1);MES(A1)) -----> in english: =DATE(YEAR(A1),DAY(A1),MONTH(A1))

c) both cases: =SI(ESNUMERO(A1);FECHA(AÑO(A1);DIA(A1);MES(A1));FECHA(DERECHA(A1;4);IZQUIERDA(A1;2);EXTRAE(A1;4;2))) -----> in english: =IF(ISNUMBER(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))

Regards!
 
Hi SirJB7,


This formulae is awesome!!


You are awesome as well :)


I need to evaluate this formulae so that I "really" understand what is going on but Thanks again for the solution.


Kind Regards,


Z.
 
Hi, mrzoogle!


When you have day-month inversion because of importing or copying cells from other sources or with different regional settings, those with day number less or equal 12 will appear as valid dates and those with day number greater 12 will appear as text. That's why there are 2 formulas embedded into 1 to consider both alternatives.


And basically both of them construct a new date using the DATE function filling the parameters Year, Month and Day according to the source.


Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.


Regards!
 
If the date is coming in text format then you may want to try following way of Text to columns as well.

1. Choose Text to columns for the column where dates are in text format

2. Choose Delimited and hit next

3. In the next window, do not change anything just press next

4. In step 3 of 3, choose radio button number 3 which says Date and against the dropdown menu choose "DMY" option.

5. Then press FINISH.


You should get what you want to do.
 
@shrivallabha

Hi!

OP discarded the use of text to columns feature.

http://chandoo.org/forums/topic/us-date-format-convert-help#post-101197

Regards!
 
@SirJB7,


I believe that is because it splits cell contents and OP wants to do it in same place. My suggestion doesn't create split contents, it just updates data at the same place.


At least that is what I think and I have written in the first line.
 
@shrivallabha

Hi!

I think you're right, maybe that's the cause for having discarded that solution. I didn't see it at a first glance.

Regards!
 
@shrivallabha

Neat trick! I so rarely use the 3rd screen of text-to-columns, I didn't even know that option was there.
 
You must log in or register to reply here.
Back
Top