Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find the number location in a cell (2003 sp1)

    How can I search for a blank space? I have a spreadsheet that I receive daily that has a date/time field with a custom format of m/d/yyyy" "h:mm:ss AM/PM
    I need to do a search for a match on just the date, but can't seem to get rid of the time no matter what format I change it to or find/search function I use. The only way I could think of that would work is to find the location of the first space between the date and time, then extract the data up to the space. Unfortunately I'm obviously doing something wrong because I get a #value error. What am I doing wrong?
    Example:c
    3/24/2006 1:35:03 AM #VALUE! formula used: =SEARCH(" ",B8)
    3/24/2006 1:35:28 AM #VALUE! formula used: =FIND("2006",B9)
    3/24/2006 1:47:19 AM #VALUE! formula used: =FIND(" ",B10)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find the number location in a cell (2003 sp1)

    With the date+time in B8, use this formula in a cell next to it:
    <code>
    =INT(B8)
    </code>
    then format the cell with the formula as a date. Date/time values are stored as numbers, with the dates being whole numbers (the number of days since the beginning of the 20th century) and the time being the fractional part. The INT function removes the fractional part, i.e. the time, leaving only the date.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    You make things sooooo easy. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •