0

I'm currently in US EST and the date format used within my company is

Month/Day/Year Hour:Minutes:sec

I need to copy and paste dates/time extracted from other regions and they have mixed formats. Some are the same as the format I have, while others are using the UK format

Day/Month/Year Hour:Min:sec

Currently, I have to manually edit the UK format to match with the rest so that my date formulas will be not have errors. This is starting to be a problem since I am facing more than 200 records, and each record has different time.

Is there a code or formula I can use to solve this issue ? I understand that the datetime format can be solved with System region settings, but this does not help since, changing to either formats will still require me to manually edit those not affected..

I'm thinking of using Visual Basic to iterate through, using each row's unique ID to identify if it is UK or US. Am I on the right track ?

Otherwise is there an excel formula or method available for this?

Thanks in advance

Community
  • 1
  • 1
Kyle
  • 915
  • 8
  • 18
  • 34
  • the question is: is the data from the other regions entered as a "date" or as a string? – MikeD Sep 08 '11 at 17:57
  • I'm guessing its string. The data gets exported from a web interface, so I can't really tell if it was exported as string or date. By the looks of it.. it seems to be string – Kyle Sep 08 '11 at 18:02
  • go into an empty cell and try adding 1 to the date. You get a numeric result ... it's a date .... you get a #VALUE error .... 't was a string ;-) I don't mean to be picky ... but it determins the strategy you want to use .... – MikeD Sep 08 '11 at 18:10
  • its definitely date then. Cos i tried doing Now() - C2 and it gives me #VALUE. C2 is the cell with the original data copied – Kyle Sep 08 '11 at 18:14
  • Just to be clear, are the dates like this `09/08/2011 12:34:56`? If so, how do you know if the date is US Sept. 8th or UK August 9th? How do you know which format it is? – PaulStock Sep 08 '11 at 19:38

1 Answers1

4

You should always aim at converting something that looks like a date into a date. A date will always remain a date and you can display it in whatever format you want. It will even survive the transition from one language to another.

So if your source is a date you are fine. If it is a string, I would do this:

  1. convert the source string using =DATEVALUE() ... this will work if the month name is in the system language
  2. if temporarily changing your system language is not an option, the next would be =DATE(yy,mm,dd) whereby the arguments must be created using =LEFT(...), =MID(...), =RIGHT(...)

  3. eventually you must convert month names from one language to another like in the following example:

    A1 contains JANFEBMAR...

    A2 contains JÄNFEBMÄR...

    conversion formula =MID(A2;FIND("MAR";A1);3)

Edit in reply to Kyle's comment:

Besides the fact that the German(Austria) locale would immediately recognize this string as a date, let's split the problem into chewable pieces. Your date string is asumed in A1

  1. the time part is easy: it is an additive term TIMEVALUE(RIGHT(A1;8))

  2. splitting the date we have to fight against seperators at dynamic locations. The positions of the delimiters can be found by

    2a) =FIND("/";A1;1) ... find position of first delimiter

    2b) =FIND("/";A1;4) ... 2nd delimiter, asuming the first figure can be only 1 or 2 char's and no blanks before - alternatively we must replace the constant "4" by term (2a)+1

    2c) =FIND("/";A1;FIND("/";A1;1)+1) ... safer version of (2b)

  3. now we have most things ready to construct our left's mid's and right's

    3a) =LEFT(A1;FIND("/";A1;1)-1) ... 1st figure ... length of (2a)-1

    3b) =MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1) ... 2nd figure ... start:=(2a)+1, num_Chars=(2c)-(2a)-1

    3c) =MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4) ... 3rd figure ... start:=(2c)+1, num_chars = 4 - asuming the year is always 4 digit. the more abstract case would be a (3b) with (2c) being a =FIND() for the first blank

How to interpret especially the 1st and 2nd figure (i.e. MM/DD or DD/MM) is up to you.

  1. now comes the fun part, i.e. concatenating all these formulae into one monster to get the date

    4a) start by entering =DATE(1;2;3)

    4b) replace 1 by (3c), 2 by (3b), 3 by (3a) ... do not copy the leading "="

    4c) =DATE(MID(A1;FIND("/";A1;FIND("/";A1;1)+1)+1;4);MID(A1;FIND("/";A1;1)+1;FIND("/";A1;FIND("/";A1;1)+1)-FIND("/";A1;1)-1);LEFT(A1;FIND("/";A1;1)-1))+TIMEVALUE(RIGHT(A1;8))

You don't see the time, only the date! .... Remember to give a custom cell format displaying date AND time, i.e. "DD.MM.YYYY hh:mm:ss"

OK ... this formula is absolutely unreadable and un-understandable, so you might want to display intermediate results in (temporary) fields/columns.

And this formula will work only if the input string is more or less strictly formated. It can cope with some added blanks at the first and second numbers, but starting with YYYY it becomes tricky. Then other concepts need to be included, like removing all blanks by =SUBSTITUTE(A1;" ";"") before all others, etc. etc.

Hint: I am always building complex formulae like this: isolate the terms in individual cells and later merge them to one large formula

MikeD
  • 8,861
  • 2
  • 28
  • 50
  • 1
    nice one - *note for internationalization:* depending on your Excel version (especially in the US or in the UK, formula separator is `,` (comma) instead of `;` (semicolon) – JMax Sep 09 '11 at 07:05
  • @JMax ... thanks to remind and sorry yes I often forget to anglicanize my formulae - I am working in the German(Austria) locale environment :-O – MikeD Sep 09 '11 at 07:26
  • Suppose I have a string 2/9/2011 15:23:40 . It is September 2nd. How do I include the time into the solution you have mentioned ? – Kyle Sep 12 '11 at 18:40