Macro to delete part of cell contents

  • Thread starter Thread starter Hugo Sondermeijer
  • Start date Start date
H

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?
 
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.
 
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.
 
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.
 
Back
Top