How to compare date & time value against date only value

[Jason P.]

I have two tables (sheets) that I am working with. One has only the date and the other has date and time in the same column. Here are examples:

Date	Count
8/22/2014
8/23/2014
8/24/2014

Date	Data
8/22/2014 20:05	1
8/22/2014 21:12	1
8/23/2014 12:15	2
8/23/2014 14:45	2
8/23/2014 16:23	2
8/24/2014 13:23	3
8/24/2014 15:20	3
8/24/2014 18:43	3

I need to count all of the entries for each day. If I use the following formula it returns a zero because the one sheet only uses date while the other includes time as well:

Please Login or Register  to view this content.

However, if I go to Sheet2, insert a new column and use the follow formula to only pull the date from the date/time cell

Please Login or Register  to view this content.

(I drag this down so that each row now has a value with only the date value in it), and compare against this new column, it works like a charm.

However, my goal is to be able to not create this additional column and just add the INT formula into the COUNTIF formula. For example, if I try doing this:

Please Login or Register  to view this content.

I get a nondiscript formula error.

Does anyone have any suggestions on how I can compare the date column to the date/time column without having to create a new column on sheet2 using the INT formula?

Thanks in advance for your help!

Jason P.

[Jacc]

Enter the formula with Ctrl + Shift + Enter instead of just enter.
=COUNTIF(Sheet2!(INT(A:A),A2)

[Yogi52o]

See attached. I used sumproduct to get the array cookin'.

[Jason P.]

Enter the formula with Ctrl + Shift + Enter instead of just enter.
=COUNTIF(Sheet2!(INT(A:A),A2)

Thank you for answering.

I tried this but it still gives me a formula error.

[Jason P.]

Thanks for your answer. While this does work, the data file that I pull the data from is constantly adding records. Therefore, I would like to be able to just reference the column instead of setting the cell range.

For example:

Please Login or Register  to view this content.

However, when I try this I get the following result: #VALUE!

Thanks again for your help!

Jason P.

[newdoverman]

On sheet2 insert a helper column so that the dates and times are in column B and enter this in column A and copy down.

Please Login or Register  to view this content.

This will extract the date only.

Then on sheet1 enter this in column B and copy down.

Please Login or Register  to view this content.

[Yogi52o]

I would just make the end point sufficiently large for your purposes. Say 7,000 or so?

[Jason P.]

On sheet2 insert a helper column so that the dates and times are in column B and enter this in column A and copy down.

Please Login or Register  to view this content.

This will extract the date only.

Then on sheet1 enter this in column B and copy down.

Please Login or Register  to view this content.

Hi Newdoverman,

Thanks for your reply. I believe that I may have not made my desire clear enough in my initial post. My goal is to be able to set a formula on sheet 1 that does everything so I don't have to make "any" changes to sheet 2. Sheet 2 is meant to be raw data that I can simply upload a new version of sheet to and all of the formula's on sheet 1 will continue to work regardless of how many rows of data are in sheet 2.

I hope this clears things up a little.

Again, thanks everyone for your help!

Jason P.

[boopathiraja]

You may try this

Please Login or Register  to view this content.

[Jason P.]

I would just make the end point sufficiently large for your purposes. Say 7,000 or so?

That would fix it in the short term but eventually there could be a tremendous amount of records and so I would like to keep the processing time down when not needed, if possible.

[Jason P.]

You may try this

Please Login or Register  to view this content.

This worked perfectly! Thanks!

[Yogi52o]

I believe the problem is in the header; it is definitively not a value that you can multiply and such. So maybe 7000 isn't sufficiently large -- what about 500000?

Or as I mentioned, if you dispensed with the header, you could get away with the A:A syntax for the column.