+ Reply to Thread
Results 1 to 5 of 5

Finding Date Time in Excel Range

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Finding Date Time in Excel Range

    Hi All,

    I need to write a code where I read DateTime from sheet and find in another sheet.

    I am ABLE to find it except for one case. If DateTime is like this: 2/18/13 0:00 excel is not able to find that in another sheet.

    Please see the attached sheet and below code I have written:
    Please Login or Register  to view this content.
    DateTime_Find.xlsm

    Can anybody please help me to solve this issue?

    Thanks in Advance!

  2. #2
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Finding Date Time in Excel Range

    Hi Tejas
    You had a few problems. Your cell formatting came through with different formats on each sheet & Find will never work like that. Find also has issues with dates, so you have to use text formatted properly. I used the american format as it looked like that's what you're using.
    You should use error control with find, as it throws errors when it doesn't find anything.
    I've attached your file with my code. Someone will most likely be able to do it in half the code lol. But this is how I do it.
    Cheers
    Phil
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Philb1; 05-24-2014 at 07:40 AM. Reason: Zeroing lngFind

  3. #3
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Finding Date Time in Excel Range

    Hi Phil.

    Thanks for your reply but at my end it is still not able to find the 2nd value in Sheet4. I downloaded the new file you uploaded and ran it.

    Please see the below screenshot.
    Find_Issue.png

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Finding Date Time in Excel Range

    This should do.
    Please Login or Register  to view this content.
    Last edited by jindon; 05-24-2014 at 10:27 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Finding Date Time in Excel Range

    Hi Tejas
    When I opened your file for the first time the cell formats were different on each worksheet & I noticed that when I opened the file just now, my cell formats were all over the place. Using a different format to your windows region settings mucks things up. India uses dd/mm/yyyy as far as I know, so if you use the same, you should be ok. I changed my cell formats back to dd/mm/yyyy & changed the format in the macro to suit & it finds both dates on Sheet4.

+ 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. Finding a sum from a column for a time range
    By mmbosman in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-15-2013, 04:14 PM
  2. [SOLVED] Finding out how many hours are used between a time range
    By lesoies in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-06-2013, 12:50 AM
  3. Finding a date in a text string and comparing it to a date range
    By ABARAN1000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2012, 02:55 PM
  4. Finding Date and Time at Max
    By ScottL in forum Excel General
    Replies: 5
    Last Post: 02-11-2011, 11:04 AM
  5. Replies: 1
    Last Post: 05-27-2006, 04:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1