Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Two sorta related questions.

    (1) On the attached example.xls, I can copy a cell, paste it into Find, and Excel tells me it canít find for what Iím looking. For example, I would like to be able to search for 02/28/2009 and have it find the first date. Any suggestions on Date-Time searching?

    (2) Is there a way to get the average between 2 different Date-Times? For example, Iíd like to get the average level for columns F,H,J (either as separate averages or one average of all 3 columns) for the period 3/1/09 4:00 to 3/2/09 16:30. Suggestions?

    Thanks for the help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    (1) Click Options >> in the Find dialog, and make sure that Excel searches in formulas, not in values. Enter the search date using the short date format of your system. (If you want to search in values, you must enter the date/time exactly as it is displayed in the cell.)

    (2) Enter the start date/time in a cell, for example L2, and the end date/time in another cell, for example L3.

    If you're using Excel 2007, you can use the new AVERAGEIFS function. In earlier versions, you can use an array formula (confirm with Ctrl+Shift+Enter):

    =AVERAGE(IF((E2:E123>=L2)*(E2:E123<=L3),F2:F123))

    (Columns H and J appear to refer to other date ranges so I didn't include them in the formula.)

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='jepalmer' post='786450' date='27-Jul-2009 13:59'](1) On the attached example.xls, I can copy a cell, paste it into Find, and Excel tells me it canít find for what Iím looking. For example, I would like to be able to search for 02/28/2009 and have it find the first date. Any suggestions on Date-Time searching?[/quote]
    In addition to Hans suggestions, to use the Find function to find dates as formatted in your spreadsheet, enter cell edit mode (F2) and copy the content within the cell, instead of copying the cell, and paste the contents into the Find dialog.
    -John ... I float in liquid gardens
    UTC -7ĪDS

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='786458' date='27-Jul-2009 21:42']In addition to Hans suggestions, to use the Find function to find dates as formatted in your spreadsheet, enter cell edit mode (F2) and copy the content within the cell, instead of copying the cell, and paste the contents into the Find dialog.[/quote]

    Just thinking outside the box here John Would you not need to find the data to be able to copy it first? And does that not then defeat the object?

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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by jepalmer' post='786450 View Post
    Just thinking outside the box here John Would you not need to find the data to be able to copy it first? And does that not then defeat the object?
    [/quote]
    Perhaps you should ask the OP how he or she is selecting that date to search for, see the OP text (quoted for your convenience). Myself, I would enter the date time I want in an appropriately formatted empty cell, then copy it into the Find box as I described. Voila, object completed!
    -John ... I float in liquid gardens
    UTC -7ĪDS

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hans et al

    Thanks!

    (even the spoilsport!)

Posting Permissions

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