+ Reply to Thread
Results 1 to 6 of 6

Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and secs.

  1. 01-12-2015, 04:56 AM #1
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and secs.

    Good Morning
    I have played around with this for some time. but short of masses of functions I cant resolve it.
    I am looking for a function likely with more embedded to convert dates - any month - of the year into ones Excel will recognise.
    When I have those I want to remove the duplicates as I will have some where there are several for the one day.

    happy with a function or a VBA solution. Thanks

    What I downloaded What I want Then I want to remove duplicates using just the the date.

    November 24, 2014 *10:18 p.m. 24/12/2014 10:18 PM 24/12/2014
    November 25, 2014 *8:01 a.m. 25/11/2014 08:01 AM 25/12/2014
    November 25, 2014 *2:33 p.m. 25/11/2014 02:33 AM
    November 25, 2014 *6:48 p.m. 25/11/2014 06:48 AM
    November 25, 2014 *9:48 p.m. 25/11/2014 09:48 AM

    I am up loading the excel sheet as i don't know if the format above will be lost.

    All the best and again thinks in advance.
    Mark
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-12-2015, 05:25 AM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and se

    This will give you just the dates, which you can then de-dupe

    So, In E3
    =INT(SUBSTITUTE(SUBSTITUTE(A3,CHAR(160),""),".",""))
    Copy down

    For de-duping
    In F3
    =INDEX($E$3:$E$81,MATCH(0,INDEX(COUNTIF($F$2:F2,$E$3:$E$81),0,0),0))
    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 01-12-2015, 10:02 AM #3
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and se

    Hi Ace.
    Thanks for your reply.
    Cold and grey here. I bet Dubai is a lot nicer today.

    I have entered your Function into my sheet. And i have tried to understand it. I have a lot more to study in Excel. But all I am getting is #VALUE!.
    Any Ideas.
    I had a problem with my PC so took a while getting back..Thanks anyhow.
    Mark

    November 24, 2014 *10:18 p.m. 24/12/2014 10:18 PM 24/12/2014 #VALUE!
    Reply With QuoteRegister To Reply
  4. 01-12-2015, 11:42 AM #4
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and se

    Very pleasant this time of the year in Dubai

    See attached sheet with formula working. I also put in an IFERROR() to give blanks in the de-dupe column
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-13-2015, 01:31 PM #5
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and se

    Hi Ace

    Snow forecast here. Most likely the sludgy stuff which melts within an hour. Not much fun to play in.

    I am flummoxed. I have your excel sheet. It all obviously worked for you. However if try and test it by highlighting cell say B59 and then use the corner click to repeat the function to the end I still get the correct answer for B59 but for the remainder I get #value. if I go to column C and do the same i get the same as you go - ie all perfect.

    I would upload this for you but I can't see how to do it.

    Mark.
    Thanks for your time Ace You have no idea how much I appreciate this. I was creating several columns - to get to the answer. OK it would work in the long run but desperately wanted the elegant solution you are giving me.

    Ah just noticed Advanced as I was about to post so file attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 01-13-2015, 01:42 PM #6
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Converts Downloaded dates into Excel dates and remove duplicates ignoring hours and se

    That's strange, not the weather but the file behavior

    The quirk is a special character ASCII code in Column A, a non-breaking source signified by CHAR(160), which I have dealt with in the formula. It seems like you are importing this data from a web source perhaps..?

    In any case if it works for some of the cells, it should work for all cells too. If you re-commit the formula by doing an F2 then Enter you'll see that it does work for Rows 59 and beyond too.
    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] Remove duplicates and change dates - VBA
    By wrightyrx7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2014, 11:50 AM
  2. [SOLVED] Search for dates whilst ignoring earlier dates for servicing
    By Ju1cy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 05-10-2013, 11:05 AM
  3. Downloaded CSV shows wrong dates in Excel
    By idahak in forum Excel General
    Replies: 2
    Last Post: 11-22-2012, 04:19 AM
  4. [SOLVED] Excel converts fractions into dates. Make it stop.
    By James Jensen in forum Excel General
    Replies: 1
    Last Post: 08-04-2006, 09:50 AM
  5. [SOLVED] converts numbers to dates
    By jason2444 in forum Excel General
    Replies: 2
    Last Post: 02-10-2006, 06:45 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1