Text to Date

[Steved]

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.

 

[oldchippy]

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

 

[SteveG]

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

 

[Gazeta]

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

 

[Marcelo]

Hi,

try to use ABS(a2)

and format it

hth
regards from Brazil
Marcelo

"Steved" escreveu:

 

[Steved]

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

 

[Gord Dibben]

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

 

[Steved]

I thankyou very much.

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