Delete Timestamp

[Gazz_85]

I can export data from my mrp system, however the date an issue occured has a
timestamp which is

DD/M/YYYY HH:MM:SS

What i want to do is be able to reformat this is that it is just DD/MM/YYYY
but whatever i do i cant remove the time even thou the format has change on
the display. I want to be able to count how issues happen in a day so use

=COUNTIF(A1:A1000,B1)

with A1 :A1000 being the range and B1 being the date i reqiure looking up in
format DD/MM/YYYYY

However it always returns 0 because of the additional time characters.

Any ideas?????????????????

Cheers

 

[Gary''s Student]

Just changing the formatting will not help you.

The date/time value is actual a number with an integer part and a fractional
part. The fractional part is the time. So if A1 contains time/date, in
another cell, say Z1, enter:

=INT(A1) and copy down.

Then =COUNTIF(Z1:Z1000,B1) will work.

 

[David Biddulph]

=SUMPRODUCT(--(INT(A1:A1000)=B1))
=SUMPRODUCT(--(A1:A1000>=B1),--(A1:A1000<B1+1))
=SUMPRODUCT((A1:A1000>=B1)*(A1:A1000<B1+1))
=COUNTIF(A1:A1000,">="&B1)-COUNTIF(A1:A1000,">="&B1+1)

or (if youn want to convert your date & time combinations to just date), use
a helper column =INT(A1) and copy down.