Excel Function Help

Discussion in 'OT Technology' started by AoyamaJPN, Dec 4, 2006.

  1. AoyamaJPN

    AoyamaJPN New Member

    Joined:
    Jul 10, 2006
    Messages:
    8
    Likes Received:
    0
    So, I have searched for hours and cannot find out how to do this...

    I have two files. The first has a "date" column which uses the NOW argument to get a date/time stamp (e.g. "12/01/06 9:36 AM").

    In the second file, I have to lookup the entires that were entered only on 12/01/06 at anytime, then look for a specific value. So, I need to look for something specific for entires only within that date (not matter what the time was).

    The problem is the date and time are together in one cell for the timestamp. And I need to only count where, for example the D column "yes" for only 12/01/06.

    thanks in advance,
     
  2. Create

    Create :free at last:

    Joined:
    Jan 4, 2006
    Messages:
    8,043
    Likes Received:
    2
    There's a few solutions. One would be to add a comparitive statement when you search, for example anything on 11/16/06 is '>= 11/16/06 12:00 AM' and '<= 11/16/06 12:00 PM'. Another would be to use a function in the excel library to parse out the piece of the date information, then run an '=' comparison.

    Sorry, but I don't know the actual syntax from memory. The concepts are both valid and exist in Excel, though.
     
  3. AoyamaJPN

    AoyamaJPN New Member

    Joined:
    Jul 10, 2006
    Messages:
    8
    Likes Received:
    0
    I still can't get it to work. I have a single file that calculates totals by using COUNTA and COUNTIF. That works great. The thing is I have another file that needs to COUNTA or COUNTIF from the other file only if the date (within the first file the date column is also timestamped eg. 12/01/06 12:00 PM) is equal to 12/01/06.

    >= 12/01/06 will get 12/02/06 also right?
     
    Last edited: Dec 4, 2006
  4. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    why not just use the TODAY() function and create another column for the time?
     
  5. Create

    Create :free at last:

    Joined:
    Jan 4, 2006
    Messages:
    8,043
    Likes Received:
    2
    You have to use a greater than AND a less than.

    I agree with HardTech, though. Create a new column in the source file that uses the date from the existing column and shows only the date, without the time. All we need is someone to point out the specific function name, as I do not know it offhand.
     
  6. AoyamaJPN

    AoyamaJPN New Member

    Joined:
    Jul 10, 2006
    Messages:
    8
    Likes Received:
    0
    Ok, I can make a column for the date. But even when I do that, for some reason, I cannot get it to work correctly. Do I need to change the date to a number to be able to lookup by date?
     
  7. HardTech

    HardTech hungry

    Joined:
    May 5, 2000
    Messages:
    28,103
    Likes Received:
    1
    Location:
    NorCal
    no

    post your formulas
     
  8. AoyamaJPN

    AoyamaJPN New Member

    Joined:
    Jul 10, 2006
    Messages:
    8
    Likes Received:
    0
    i want to do this:

    =COUNTIF('[file.xls]sheet'!$G$4:$G$91,"Yes")*COUNTIF('[file.xls]sheet'!$D$4:$D$91,"12/1/06")

    or

    =COUNTIF('[file.xls]sheet'!$G$4:$G$91,"Yes")*AND('[file.xls]sheet'!$D$4:$D$91,"12/1/06")
     
    Last edited: Dec 4, 2006
  9. AoyamaJPN

    AoyamaJPN New Member

    Joined:
    Jul 10, 2006
    Messages:
    8
    Likes Received:
    0
    no help?
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I would use the trunc command. For example:

    A1=now()
    A2=today()
    A3=if(trunc(a1)=a2,"True","False")

    A3 would equal True because trunc removes the time portion of the date
     

Share This Page