Insert ' sign infront of dates

[FIRSTROUNDKO via OfficeKB.com]

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

 

[Ron Rosenfeld]

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

 

[Ed Ferrero]

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

 

[FIRSTROUNDKO via OfficeKB.com]

Ron

I dont mind using VBA

Thanks

Darren

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

 

[Dave O]

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

 

[Ron Rosenfeld]

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