Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
timestamp | Excel Worksheet Functions | |||
Timestamp | Excel Discussion (Misc queries) | |||
Timestamp | Excel Discussion (Misc queries) | |||
Timestamp | Excel Discussion (Misc queries) | |||
Now as timestamp | Excel Worksheet Functions |