Hi!

i have the current visable values

May-06
Jun-06
Jul-06
Aug-06

How do I insert ' (Quote sign) in front of dates in Col A so the underlying
values

01/05/2006
01/06/2006
01/07/2006
01/08/2006

becomes the visable values

'01/05/2006
'01/06/2006
'01/07/2006
'01/08/2006

Thanks In advance

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200609/1

On Thu, 28 Sep 2006 11:07:09 GMT, "FIRSTROUNDKO via OfficeKB.com" <u15639@uwe
wrote:

Hi!

i have the current visable values

May-06
Jun-06
Jul-06
Aug-06

How do I insert ' (Quote sign) in front of dates in Col A so the underlying
values

01/05/2006
01/06/2006
01/07/2006
01/08/2006

becomes the visable values

'01/05/2006
'01/06/2006
'01/07/2006
'01/08/2006

Thanks In advance

Difficult without VBA.

However, you could:

1. Format/Cells/Number/Custom Type: dd/mm/yyyy

or use the formula in an adjacent column:

=TEXT(A1,"dd/mm/yyyy")


--ron

Hi,

Format -- Cells...

Number Tab

Custom Format

Enter the next line as the format
"'" d-mm-yy

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

Ron

I dont mind using VBA

Thanks

Darren

Ron Rosenfeld wrote:
Hi!

[quoted text clipped - 21 lines]

Thanks In advance

Difficult without VBA.

However, you could:

1. Format/Cells/Number/Custom Type: dd/mm/yyyy

or use the formula in an adjacent column:

=TEXT(A1,"dd/mm/yyyy")

--ron

--
Message posted via http://www.officekb.com

This code will do it: paste this in as a macro, then highlight the
range of dates in question and run.

Sub Add_Apostrophe()
Dim rCell As Range

For Each rCell In Selection.Cells
rCell.Value = "'"& rcell.value
Next rCell
End Sub

On Thu, 28 Sep 2006 12:26:29 GMT, "FIRSTROUNDKO via OfficeKB.com" <u15639@uwe
wrote:

Ron

I dont mind using VBA

Thanks

Darren

The simplest way would be to format the cells the way I suggested unless you
absolutely require a text string instead of an Excel recognizable date.

Does the formatting not provide you with what you want? For most purposes, I
would have thought that would be better.

If that is not a better solution, you could do what you want with a VBA macro:

=========================
Option Explicit

Sub FormatDate()
Dim c As Range
For Each c In Selection
If IsDate(c.Value) Then
c.Value = "'" & Format(c.Value, "dd/mm/yyyy")
End If
Next c
End Sub
=======================

Of course, the dates would now be text strings and not easily usable by Excel
as dates. They might seem to be usable, but how they would be interpreted
would be dependent on the Windows Regional Settings.

In other words, on my (US-centric) machine, '01/05/06' would be interpreted as
5 Jan 2006; On a different machine, it might be interpreted as 1 May 2006.
--ron