Delete Timestamp
 

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

Delete Timestamp
 

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.


--
Gary''s Student - gsnu200907


"Gazz_85" wrote:

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

Delete Timestamp
 

=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.
--
David Biddulph

"Gazz_85" wrote in message
...
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