Hi,

I need to evaluate data based on date but, unfortunately, the string that I
have to work with is as follows:

4/3/2006 13:22

I'm not concerned with the time stamp. I need to reduce it to 4/3/2006 and
then be able to seperate out the day, month and year. This is so I can show
'X' amount of items in a year, in a month, etc.

I've done this before but it's always been a bit tedious.

I can change the format of the cell to Day-Month-Year but the time stamp
remains which causes problems with calculations.

Is there an easy way to get rid of the time stamp and seperate out the
calendar items?

If there are 150 to 300 of these items entered in any given year and I want
to view just the year, it's a HUGE pain to have to put them into a pivot
table and uncheck the years or months I don't want!

Thanks in advance!

Zone,

I don't have an exact answer to your question as I do not fully under
stand what you are asking.

I think that a few things might be very helpful to you though.

Firstly, excel stores all of it's dates as numbers.

This means that you can subtract 2 dates and then if you format the
result to be a number you will get the amount of days between the 2
numbers.

Also it is stored with the whole numbers representing the date and the
decemials representing the time. if you use a formula like
=LEFT(B1,5), where b1 is the cell that the date is in and 5 is the
length of the whole number than you can seperate the date from the
number. Also the formula =FIND(".",B1) works and my testing shows that
it works better.

If it is a real date plus time then using

=INT(B1)

is enough



--
Regards,

Peo Sjoblom



"weezer007" wrote in message
oups.com...
Zone,

I don't have an exact answer to your question as I do not fully under
stand what you are asking.

I think that a few things might be very helpful to you though.

Firstly, excel stores all of it's dates as numbers.

This means that you can subtract 2 dates and then if you format the
result to be a number you will get the amount of days between the 2
numbers.

Also it is stored with the whole numbers representing the date and the
decemials representing the time. if you use a formula like
=LEFT(B1,5), where b1 is the cell that the date is in and 5 is the
length of the whole number than you can seperate the date from the
number. Also the formula =FIND(".",B1) works and my testing shows that
it works better.

EDIT: the formula =FIND(".",B1) should have been =VALUE(LEFT(B1,
(FIND(".",B1))-1))