Excel using date/time with multiple different regi

Question

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

Answer 1

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