Macro to delete part of cell contents

[Hugo Sondermeijer]

How can I delete the following date/time-contents of one cell and
apply it to a column of 1400 cells, for example:

01-Jan-03 12:12:00

Now I would like to delete the 12:12:00, which is 2 spaces reomoved
from the date.

I have tried the edit->exclude function without success, hence I think
I need a macro that recognises the time format (xx:xx:xx) and deletes
it or one that can backspace 10 keystrokes, since all cells have the
same date/time format.

How can I do this?

 

[Jim Rech]

Select your 1400 cells and open the cell formatting dialog with Format,
Cells. On the Number tab pick Date and any date format you want (one w/o
the time).

The "time" you see is not really _in_ the cell. It's just part of the cell
format you're using. By changing the date format, the "time" goes away.

 

[Hugo Sondermeijer]

Thanks for your quick response Jim. There is however one problem with
that. I need to compare 2 rows of 1400 cells by date using the
TEXT-EXACT function. One row actually contains date without time at
all, the other row with time as you mentioned it. Using the TEXT-EXACT
function to compare 2 cells, the cell "with" the time returns a
different value than the cell without it, hence the function will
always return FALSE. I think I need a macro that can remove the times
from the cells with time in them to compare them to the cells without
time at all.

 

[Dave Peterson]

Not to give up yet...

couldn't you just compare the integer portion of the date/times?

=if(int(a1)=int(b1),"same date","different dates")
or even:
=if(text(a1,"mm/dd/yyyy")=text(b1,"mm/dd/yyyy"),"same","different")

But if you want to change the values:

Option Explicit
Sub testme02()
Dim myCell As Range

For Each myCell In Selection.Cells
With myCell
If IsDate(.Value) Then
.Value = Int(.Value)
.NumberFormat = "mm/dd/yyyy"
End If
End With
Next myCell

End Sub


You may be able to do Data|Text to columns and just separate the date from the
time (and do not import the time). And format the cells as a nice date when
you're done.