Results 1 to 7 of 7

Convert text m/d/yyyy hh:mm:ss to date dd/mm/yyyy hh:mm

Threaded View

  1. #1
    Registered User
    Join Date
    04-23-2023
    Location
    Australia
    MS-Off Ver
    365
    Posts
    1

    Convert text m/d/yyyy hh:mm:ss to date dd/mm/yyyy hh:mm

    So i've been banging my head against the wall on this and tried various solutions to no avail.

    I work for a US based company, but am based in Australia. On one of the reports we run, I get plain text dates formatted m/d/yyyy hh:mm:ss, which no matter which method I try, I can't seem to get d/m/yyyy hh:mm. I'm not even fussed about the exact number of characters, dd vs d or hh:mm:ss vs hh:mm.

    If I ditch the time component, I can convert the dates around using
    =DATE(RIGHT(A2,4),LEFT(A2,SEARCH("/",A2)-1),TRIM(MID(LEFT(A2,SEARCH("/",A2,SEARCH("/",A2)+1)-1),SEARCH("/",A2)+1,2)))
    , but I can't seem to work out how to retain the time.

    Any help or guidance would be much appreciated. Some sample fields as below:

    Original:
    3/20/2023 9:20:59 AM
    3/20/2023 10:36:21 AM
    3/18/2023 6:44:06 PM
    3/19/2023 11:46:52 AM
    3/17/2023 9:25:03 AM


    Desired format (or something resembling this, I'm not precious as long as days come before months):

    20/03/2023 9:20 AM

    20/3/2023 9:20:59 AM is totally fine if easier.
    Last edited by lpie90; 04-23-2023 at 09:39 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-20-2020, 05:31 PM
  2. Convert Date format from Text format reading m/d/yyyy to dd/mm/yyyy
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2017, 11:22 PM
  3. Date Function: How to convert MM-DD-YYYY to DD-MM-YYYY
    By ramki in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-23-2014, 12:03 AM
  4. [SOLVED] Issue in macro to convert date from DD-MON-YYYY into YYYY-MON-DD
    By abraham30 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-20-2014, 09:17 AM
  5. Convert dd/mm/yyyy DATE >> to mm/yyyy TEXT
    By Mr.X in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-05-2014, 10:59 PM
  6. [SOLVED] convert MM/DD/YYYY to DD/MM/YYYY while the data format is text
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-18-2012, 02:43 PM
  7. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1