+ Reply to Thread
Results 1 to 6 of 6

Two date-arrays with different formats. How to get same format.

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Two date-arrays with different formats. How to get same format.

    Hi,

    i have one file which has a date column in this format:
    "1/15/17"

    i have another file which has a date column in this format:
    "1/15/2017 3:00:44 PM"

    I want to compare these two dates............and not the "times"..........

    In the example i show above the dates are the same => 1/15/17

    How do i convert "1/15/2017 3:00:44 PM" to "1/15/17"

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Two date-arrays with different formats. How to get same format.

    If they are proper date/times (as Excels understands them) then you can just use:

    =INT(A1)

    to remove the time element. I suspect, however, that they are text values which just look like date/times, so if this is the case you can use this:

    =--LEFT(A1,FIND(" ",A1)-1)

    Format as a date in the style you prefer, then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Two date-arrays with different formats. How to get same format.

    Hello welchs101,

    Pete_UK is correct. Make sure your Data is numbers and not just Text which appears like numbers.

    Highlight the Range you wish to change and Format it with "NumberFormat" = "m/d/yyyy"

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Two date-arrays with different formats. How to get same format.

    And if i am doing this in VBA and not formulas can i still use "NumberFormat" = "m/d/yyyy"

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Two date-arrays with different formats. How to get same format.

    NumberFormat is normally used with a range, so unless you are putting the values into a range you can't use it.
    If posting code please use code tags, see here.

  6. #6
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Two date-arrays with different formats. How to get same format.

    Hello welchs101,

    Thanks for the Rep welchs101, I appreciate it.

    Regards.
    Last edited by Winon; 06-06-2017 at 07:45 AM. Reason: Spelling

+ 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. Convert Multiple Date Formats Into Standard Format
    By Abdur_rahman in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-29-2016, 02:04 PM
  2. 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
  3. Replies: 2
    Last Post: 06-02-2015, 08:42 AM
  4. [SOLVED] mixed date formats in s/s, how to format
    By cal_chica in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-16-2014, 06:22 PM
  5. Insert Worksheet - All formats are already in Date Format
    By captbluefin in forum Excel General
    Replies: 0
    Last Post: 04-22-2013, 09:41 AM
  6. Converting all date formats to one single standard format.
    By booo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-25-2010, 07:14 PM
  7. Replies: 5
    Last Post: 10-18-2010, 05:54 AM

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