Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006 7:51:09
AM.

Any help please.
Thankyou.

Steved Wrote:
Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is
not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug,
no
matter when I try change it in Custom to dd-mm I still get 8/8/2006
7:51:09
AM.

Any help please.
Thankyou.
Hi Steved

Put 1 in a spare cell, copy it, select your dated cell

i.e. 8/8/2006 7:51:09 AM , then paste special multiply

Format Date Custom dd-mmm

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569185

Type a 1 in a cell not formatted as Text. Copy. In your Text formatted
cell, Paste Special and select Multiply. Now you should be able to
format.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=569185

U¿ytkownik "Steved" napisa³ w wiadomo¶ci
...
Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006
7:51:09
AM.

Any help please.
Thankyou.

u cannot format text like that
to format this txt as date u got to use mix of formulas date,
year,month,day, hour, second to change it into number ( which is then
formatted as date )
mcg

Hi,

try to use ABS(a2)

and format it

hth
regards from Brazil
Marcelo

"Steved" escreveu:

Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006 7:51:09
AM.

Any help please.
Thankyou.

Hello from Steved

I've done exactly what you have asked, and yes it had done exatly as you all
said, however I run the below vba and exch time I change the cell it reverts
back to 8/8/2006 9:25:11, Is there please another answer.
Thankyou

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:AB272"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Me.Range("B3") = Now
End If
Application.EnableEvents = True
End If
End With
End Sub




"Steved" wrote:

Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006 7:51:09
AM.

Any help please.
Thankyou.

Steved

Try this small adjustment.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:AB272"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Me.Range("B3") = Format(Now, "dd mmm")
End If
Application.EnableEvents = True
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 7 Aug 2006 14:27:01 -0700, Steved
wrote:

Hello from Steved

I've done exactly what you have asked, and yes it had done exatly as you all
said, however I run the below vba and exch time I change the cell it reverts
back to 8/8/2006 9:25:11, Is there please another answer.
Thankyou

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:AB272"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Me.Range("B3") = Now
End If
Application.EnableEvents = True
End If
End With
End Sub




"Steved" wrote:

Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006 7:51:09
AM.

Any help please.
Thankyou.

I thankyou very much.

"Gord Dibben" wrote:

Steved

Try this small adjustment.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:AB272"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Me.Range("B3") = Format(Now, "dd mmm")
End If
Application.EnableEvents = True
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Mon, 7 Aug 2006 14:27:01 -0700, Steved
wrote:

Hello from Steved

I've done exactly what you have asked, and yes it had done exatly as you all
said, however I run the below vba and exch time I change the cell it reverts
back to 8/8/2006 9:25:11, Is there please another answer.
Thankyou

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A5:AB272"), .Cells) Is Nothing Then
Application.EnableEvents = False
If Not IsEmpty(.Value) Then
Me.Range("B3") = Now
End If
Application.EnableEvents = True
End If
End With
End Sub




"Steved" wrote:

Hello from Steved

I formatted a cell in text now I want to custom it to dd-mmm, It is not
allowing me to acheive this.

8/8/2006 7:51:09 AM this is the result, I would like it to be 8-Aug, no
matter when I try change it in Custom to dd-mm I still get 8/8/2006 7:51:09
AM.

Any help please.
Thankyou.