+ Reply to Thread
Results 1 to 12 of 12

Looking for a formula to convert American dates to British dates.

  1. 02-21-2017, 07:12 AM #1
    JackMWhit
    JackMWhit is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Blackpool, England
    MS-Off Ver
    2013
    Posts
    9

    Question Looking for a formula to convert American dates to British dates.

    *Example is now attached.*

    At work, when I export the data from our booking system I'm left with a huge mess to try filter through to fix up so I can use the data effectively.

    I've been able to refine the messy data into the same format (MM/DD/YYYY). However, I'm English and the rest of our dates are in British format (DD/MM/YYYY).

    I know that there are functions I can use in Excel to overcome this issue with relative easy, however, for certain reasons I'm unable to use these features on this document.

    Does anyone have an idea for a forumla that can fix this issue? Even if it's as simple as a formula that just swaps certain characters?

    Any and all help is vastly appreciated.
    Attached Files Attached Files
    Last edited by JackMWhit; 02-21-2017 at 07:20 AM. Reason: Uploading example
    Reply With QuoteRegister To Reply
  2. 02-21-2017, 07:16 AM #2
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Looking for a formula to convert American dates to British dates.

    Perhaps Try

    =TEXT(A1,"MM/DD/YYYY")
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 02-21-2017, 07:19 AM #3
    JackMWhit
    JackMWhit is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Blackpool, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Looking for a formula to convert American dates to British dates.

    Quote Originally Posted by shukla.ankur281190 View Post
    Perhaps Try

    =TEXT(A1,"MM/DD/YYYY")
    Thanks for the reply, sir.
    I've attached the example now if you want to review it.
    I don't quite know what to use that formula to my advantage. As you can see I have something similar on at the moment. Maybe I'm misusing it?
    Reply With QuoteRegister To Reply
  4. 02-21-2017, 07:42 AM #4
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Looking for a formula to convert American dates to British dates.

    ok Try

    B3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This is craziness but perhaps it would work.
    Reply With QuoteRegister To Reply
  5. 02-21-2017, 07:48 AM #5
    JackMWhit
    JackMWhit is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Blackpool, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Looking for a formula to convert American dates to British dates.

    Quote Originally Posted by shukla.ankur281190 View Post
    ok Try

    B3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This is craziness but perhaps it would work.
    Holy ****.
    Sir, you're a genius. Thank you very much for your help!
    I'm very impressed!

    + Rep for sure
    Reply With QuoteRegister To Reply
  6. 02-21-2017, 07:51 AM #6
    shukla.ankur281190
    shukla.ankur281190 is offline
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Looking for a formula to convert American dates to British dates.

    hmmmm thanks for rep and feedback if you will add +0 in last of formula hope formula will read actual date format as per your system and all the dates will be convert in actual dates instead text. Check once...
    Last edited by shukla.ankur281190; 02-21-2017 at 07:52 AM. Reason: missing info
    Reply With QuoteRegister To Reply
  7. 02-21-2017, 07:57 AM #7
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,180

    Re: Looking for a formula to convert American dates to British dates.

    There is no foolproof way of doing this: 01/02/2017 is 1st Feb (UK) or 2nd Jan (US) so we don't know which it is: the dates where the "month" > 12 can be reversed.

    Changed formula in B

    =IF(ISNUMBER(A3),TEXT(A3,"dd/mm/yyyy"),TEXT(RIGHT("0" &LEFT(A3,FIND(" ",A3)),11),"dd/mm/yyyy"))

    Formula in C

    =IF(MID(B3,4,2)+0>12,MID(B3,4,2) & "/" & MID(B3,1,2) & "/" &RIGHT(B3,4),B3)
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 02-21-2017, 08:04 AM #8
    JackMWhit
    JackMWhit is offline
    Registered User
    Join Date
    02-20-2017
    Location
    Blackpool, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Looking for a formula to convert American dates to British dates.

    Quote Originally Posted by JohnTopley View Post
    There is no foolproof way of doing this: 01/02/2017 is 1st Feb (UK) or 2nd Jan (US) so we don't know which it is: the dates where the "month" > 12 can be reversed.

    Changed formula in B

    =IF(ISNUMBER(A3),TEXT(A3,"dd/mm/yyyy"),TEXT(RIGHT("0" &LEFT(A3,FIND(" ",A3)),11),"dd/mm/yyyy"))

    Formula in C

    =IF(MID(B3,4,2)+0>12,MID(B3,4,2) & "/" & MID(B3,1,2) & "/" &RIGHT(B3,4),B3)
    Thank you for help again John, you also provided invaluable assistance to me yesterday.
    And yes I'm sorry that I could've helped a bit more with helping you to understand which dates were which!
    Thank you for going the extra mile and doing both - I'm very grateful
    Reply With QuoteRegister To Reply
  9. 02-21-2017, 08:07 AM #9
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,180

    Re: Looking for a formula to convert American dates to British dates.

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  10. 01-23-2020, 05:47 AM #10
    elise_emma
    elise_emma is offline
    Registered User
    Join Date
    01-23-2020
    Location
    Oxfordshire
    MS-Off Ver
    windows 10
    Posts
    1

    Re: Looking for a formula to convert American dates to British dates.

    Amazing - Thank you for this!!!
    Reply With QuoteRegister To Reply
  11. 11-10-2021, 02:10 PM #11
    llewis79
    llewis79 is offline
    Registered User
    Join Date
    03-17-2021
    Location
    Sheffield
    MS-Off Ver
    10
    Posts
    1

    Thumbs up Re: Looking for a formula to convert American dates to British dates.

    This really helped me and saved me hours, thank you.
    Reply With QuoteRegister To Reply
  12. 06-28-2022, 02:42 PM #12
    Wowee1511
    Wowee1511 is offline
    Registered User
    Join Date
    06-28-2022
    Location
    Nottingham England
    MS-Off Ver
    365
    Posts
    1

    Re: Looking for a formula to convert American dates to British dates.

    Adding my thanks for this - I have to do accounts for many online businesses, who get many reports in U.S format.
    I was going to try to work this out for myself, You have saved me hours...

    Thank You.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] American to British Date
    By namluke in forum Excel General
    Replies: 3
    Last Post: 08-12-2015, 10:27 AM
  2. Problem importing British dates
    By Sten in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-11-2012, 09:40 AM
  3. American/British Date format issue
    By hriggs in forum Excel General
    Replies: 4
    Last Post: 05-29-2008, 08:09 AM
  4. dates on a userform are american!!!
    By excely in forum Excel General
    Replies: 1
    Last Post: 04-14-2008, 07:31 AM
  5. [SOLVED] what is the formula from british pounds to American dollars
    By MAMAITO in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-10-2006, 11:35 AM
  6. Excel American dates
    By Duncan in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-12-2006, 09:30 AM
  7. convert american dates from a query
    By Mika in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-02-2005, 06:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1