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

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

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

Chris,
It removes the time element as time is held as decimal
between 0 and 1.

Hence today at 17:00 is 38817.71 so date element is 38817 i.e. integer part
of the number. If your data is in C, insert a column next to C and put =C1
(or whatever first row is). Copy down and select this added
column==Copy=Past Special -Values (same column) and then delete column C.

TEST first!


HTH

"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

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

Thanks very much it works great.

Regards

Chris

"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

Tom,

Thanks for reading and replying to my post.

The code works a treat and it's actually sorted out another problem I had
with my spreadsheet.

Thanks very much.

Regards

Chris

"Tom Ogilvy" wrote:

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