+ Reply to Thread
Results 1 to 15 of 15

Different date formats in same source

  1. #1
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Different date formats in same source

    Hey, glad to be back here!

    I am automating some worksheets so that I can hand them over to someone else without them having to do (ideally any) manual effort. The file handles timesheet entries (people log the hours they work) and from there I run a pivot table that shows me hours worked per week/month/project etc

    The source of my spreadsheet is a time & date field, coming from an external system. The values are exported in 2 US formats:

    02/14/2019 07:15 AM - so mm/dd/yyy hh:mm AM
    or
    02/06/2019 08:32 - so mm/dd/yyy hh:mm

    Using my manual solution I use the text to column to get these entries into a manageble dd/mm/yyyy format which Excel recognises. However, I want to avoid doing this type of manual manipulation if I can help it, so I just want to do this using formulas.

    How would you do this? My attempts using TEXT, LEFT, WEEKDAY have not worked just yet.
    Last edited by deemo; 12-30-2019 at 10:17 AM.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Different date formats in same source

    Try

    =TEXT(A1,"dd/mm/yyyy")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Thanks for your reply Ace_XL. Unfortunately this doesn't do the trick in the first format.
    Find very small file attached. How can I get to WEEKDAY in column C?
    Attached Files Attached Files

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Different date formats in same source

    Odd, the file you attached gives me correct anticipated results.

    If you're looking for WEEKDAY, simply reference the data source i.e. Col. A of your sample rather than Col. B

    =WEEKDAY(A2)

    Does that work for you>

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Different date formats in same source

    Your second value is text in your example, you may need to change the , to ; in your locale


    =IF(ISNUMBER(A3),INT(A3),DATEVALUE(LEFT(A3,10)))

  6. #6
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Applying WEEKDAY

    to

    02/14/2019 07:15 AM

    gives me a #VALUE error

  7. #7
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Thanks davsth. This doesn't work for me either on the second value. If I do text->columns to strip off the AM it does work but want to avoid that.

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Different date formats in same source

    which is why you apply it to the above in #5

    or

    =weekday(IF(ISNUMBER(A3),INT(A3),DATEVALUE(LEFT(A3,10))) )

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Different date formats in same source

    IF(ISNUMBER(A3),INT(A3),DATE(mid(a3, 7,4),LEFT(A3,2),mid(a3,4,2)))

    any better?

  10. #10
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Perhaps the locale is the issue. Whatever I do, row 3 gives me errors on every formula. See attached:

    PS really appreciating you taking the time to help out!
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Quote Originally Posted by davsth View Post
    IF(ISNUMBER(A3),INT(A3),DATE(mid(a3, 7,4),LEFT(A3,2),mid(a3,4,2)))

    any better?
    Yes, perfect!! Awesome, thanks a lot.

  12. #12
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Nearly there... the formula is nearly perfect.

    Remaining problem is that when ISNUMBER is TRUE, the month and day are switched so something goes wrong with the INT function.

    In the attached you see how the date shown is 11-Jun but in fact it should be 6 November.
    Attached Files Attached Files

  13. #13
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Different date formats in same source

    Try this amended version of davsth's formula

    =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2, 7,4),LEFT(A2,2),MID(A2,4,2)))

  14. #14
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Different date formats in same source

    Hi Deemo, if you look at the formatting of those first 2 cells, they are NOT in USA format, they are formatted as d/mm/yyyy h:mm

  15. #15
    Registered User
    Join Date
    03-16-2009
    Location
    London, UK
    MS-Off Ver
    Excel 16.78 on Mac - Office 365.
    Posts
    80

    Re: Different date formats in same source

    Quote Originally Posted by Ace_XL View Post
    Try this amended version of davsth's formula

    =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(MID(A2, 7,4),LEFT(A2,2),MID(A2,4,2)))
    That did the trick, thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Preventing paste formats from other source
    By Sean Thomas in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2019, 05:15 PM
  2. [SOLVED] Select cells in source range based on date from another source range & import to new book
    By mattman1968 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-24-2019, 03:31 PM
  3. Replies: 2
    Last Post: 01-31-2017, 10:00 PM
  4. Copying Source Formats using Intersect ranges
    By pdalal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2016, 05:30 PM
  5. Date Format Formulam - Mixture of date formats and seperators
    By kp1983 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2016, 02:12 PM
  6. Linking Data from Source files in unusual formats
    By amartin11 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-17-2015, 01:35 PM
  7. VBA:comboboxes to present same date in different formats...ADAPT WITH CHANGE IN DATE
    By AlexDobbin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2015, 08:30 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