I have a date/time in the form of:

12/24/2006 4:21:49 AM

I want only 12/24/2006.

How do I get rid of 4:21:49 AM ?


Formating *shows* only 12/24/2006
but the time is still part of the data.

If I try:

ActiveCell.Formula = Left(ActiveCell.Formula, 10)
or
ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10)

the string is not shortened
but the number of seconds seems to diminish.

Thank you,
Emory

XL stores date/times as integer and fractional offsets from a base date.


Try entering this in the immediate window:

?CDbl(Date), CDbl(Time), CDbl(Now)

So

ActiveCell.Value = Int(ActiveCell.Value)

will get rid of the time portion.


In article <(E-Mail Removed)>,
Emory Richter <(E-Mail Removed)> wrote:

> I have a date/time in the form of:
>
> 12/24/2006 4:21:49 AM
>
> I want only 12/24/2006.
>
> How do I get rid of 4:21:49 AM ?
>
>
> Formating *shows* only 12/24/2006
> but the time is still part of the data.
>
> If I try:
>
> ActiveCell.Formula = Left(ActiveCell.Formula, 10)
> or
> ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10)
>
> the string is not shortened
> but the number of seconds seems to diminish.
>
> Thank you,
> Emory

ActiveCell = DateValue(ActiveCell)

Hth,
Merjet

Hi jemcgimpsey

Thank you for the explaination
and the code
which works fine.

Emory

In article <jemcgimpsey-(E-Mail Removed)>,
(E-Mail Removed) says...
> XL stores date/times as integer and fractional offsets from a base date.
>
>
> Try entering this in the immediate window:
>
> ?CDbl(Date), CDbl(Time), CDbl(Now)
>
> So
>
> ActiveCell.Value = Int(ActiveCell.Value)
>
> will get rid of the time portion.
>
>
> In article <(E-Mail Removed)>,
> Emory Richter <(E-Mail Removed)> wrote:
>
> > I have a date/time in the form of:
> >
> > 12/24/2006 4:21:49 AM
> >
> > I want only 12/24/2006.
> >
> > How do I get rid of 4:21:49 AM ?
> >
> >
> > Formating *shows* only 12/24/2006
> > but the time is still part of the data.
> >
> > If I try:
> >
> > ActiveCell.Formula = Left(ActiveCell.Formula, 10)
> > or
> > ActiveCell.Formula = Mid(ActiveCell.Formula, 1, 10)
> >
> > the string is not shortened
> > but the number of seconds seems to diminish.
> >
> > Thank you,
> > Emory
>

In article <(E-Mail Removed)>,
(E-Mail Removed) says...
> Merjet
>

Merjet
Thank you for your answer.
Emory