COUNTIF help DATE condition and DATE TIME range

[sparkd]

I need some help please?

I have a range of cell B1:B500

These cells are TEXT from a Database output

format is

dd/mm/yyyy hh:mm:ss


In cell H7 I want to count the number of occurrences in the range, based on a prescribed date in cell G7

The issue I currently face is that each value is counted as one instance due to the HH:MM:SS elements in the range

 

[steve the fish]

Try:

=COUNTIFS($B$1:$B$500,">="&G7,$B$1:$B$500,"<"&G7+1)

If that doesnt work then try to use text to columns and see if that converts them. Saving that come back and ill write it differently.

 

[Special-K99]

=sumproduct(--(left(b1:b500,10)+0=g7))

 

[Special-K99]

Thinking...

 

[steve the fish]

No as that would be incorrect. That would be tomorrow. Its essentially saying greater than or equal to the first second of today or less than the first second of tomorrow.

 

[Captain_Conman]

If you do not need the date and time in the same cell, you could take the following approach...

1. Highlight Column B
2. Go to the data tab in the ribbon
3. Select "Text to Columns"
4. Select "Delimited"
5. Select "Next"
6. Deselect "Tab" & Select "Space"
7. Select "Next"
8. Set your "Destination" as the next blank column in your spreadsheet
9. Select Finish

This will separate your data into a "Date" column and a "Time" column. You can now format them as such and your countif should work properly when referencing the date.

 

[steve the fish]

If it is text and the format follows your local date protocols then you can say:

=SUMPRODUCT(($B$1:$B$500>=G7)*($B$1:$B$500 < G7+1))<g7+1))<g7+1))< html=""></g7+1))<g7+1))<>

 

[Special-K99]

No as that would be incorrect. That would be tomorrow. Its essentially saying greater than or equal to the first second of today or less than the first second of tomorrow.

Yep, i see what you did there after I posted