insert a new column D (select column D and do Insert = Columns

In d1 or d2 put in
=trunc(C1)
or trunc(c2)

then drag fill down the column

select this column D and do Edit=copy, then Edit=Paste special and select
Values

now with D select, do format = cells and format as dd/mm/yyyy

If you are satisfied with the results, then select column C (the original
data) and delete it or maintain both columns.

Yes, it does remove the time portion. Data/times are stored as the number
of days from a base date. so .5 would be Noon (.5 x 25 = 12 hours from the
base date).


Using the immediate window to show how Dates and times are stored:

? cdbl(now)
38817.5518981482
? now
04/10/2006 1:14:49 PM

so today is 38817 days from the base date. Truncating the decimal portion
removes the time element.

--
regards,
Tom Ogilvy



"ir26121973" wrote:

Yes thanks your previous reply did arrive - please see my note of thanks in
my post.

Thanks for this function, I've tried it but it doesn't seem to work. My
range for this data is column C, should the formula go in these cells?

Can you also please tell me, does this actually take the time element of
data from the cell, rather than just change the format?

Thanks and regards

Chris

"Toppers" wrote:

Hi,
Not sure if my previous post arrived.

in b1 (formated as dd/mm/yyyy) put

=int(a1) where A1 is Date + time

"ir26121973" wrote:

Hi,

Wonder if someone can help me please.

I have a spreadsheet that contains a huge amount of data, one element of
which is a date and time field.

Once I have sorted through this data I then import it to a database. The
only problem is when I run a date query in the DB, because of the time
element it doesn't work correctly and in all honesty I don't need the time
element of data.

Can someone tell me please how I would programatically take out the time
element from this range of data. I know I can format the field just to show
the date, but the data with the time is still present in the cell.

The format for the column of data is currently dd/mm/yyyy hh:mm if that
helps. I did look at other ways to try and work with the data as it was
(thanks to those who provided the information then), but I feel this is the
best way to deal with it.

Thanks and regards

Chris