# Excel Function Help

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

1. ### AoyamaJPNNew Member

Joined:
Jul 10, 2006
Messages:
8
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.

2. ### Create:free at last:

Joined:
Jan 4, 2006
Messages:
8,042
114
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. ### AoyamaJPNNew Member

Joined:
Jul 10, 2006
Messages:
8
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. ### HardTechhungry

Joined:
May 5, 2000
Messages:
28,095
2
Location:
NorCal
why not just use the TODAY() function and create another column for the time?

5. ### Create:free at last:

Joined:
Jan 4, 2006
Messages:
8,042
114
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. ### AoyamaJPNNew Member

Joined:
Jul 10, 2006
Messages:
8
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?

Joined:
May 5, 2000
Messages:
28,095
2
Location:
NorCal
no

8. ### AoyamaJPNNew Member

Joined:
Jul 10, 2006
Messages:
8
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

Joined:
Jul 10, 2006
Messages:
8
0
no help?

Joined:
Mar 14, 2000
Messages:
14,523