Help formatting date

[Guest]

Hi,
I have this date and time value (Friday, October 15, 2004 10:35:12) stored
in a cell ("E80"). I need to format it to 10/15/04 and paste it in another
spreadsheet. What is doing right now is changing to today's day.... I just
need the date, ignore the time.

I've tried Format(Date, "mm/dd/yy") and this is the one giving me todays
date...

Help... this formatting date is not making my date ...

 

[JulieD]

Hi Gaba

right mouse click on cell E80
choose format cells
on the number tab choose custom
in the white line top right type

mm/dd/yy
click Ok

Cheers
JulieD

 

[Bob Phillips]

Presuming you want a VBA solution

With Worksheets("Sheet2").Range("E80")
.Value = Worksheets("Sheet1").Range("A1").Value
. NumberFormat = "mm/dd/yy")
End With

 

[Guest]

Thanks JulieD and Bob for your answers... still not working. I can't believe
something so simple is giving me such a hard time. I think I know what the
problem is. Soon as I take the day part ("Friday,") it works (both solutions)
perfect.
I need to find a way to Left Trim to the first ","

I'll keep trying

 

[Bob Phillips]

Mine had a type, should be

With Worksheets("Sheet2").Range("E80")
.Value = Worksheets("Sheet1").Range("A1").Value
. NumberFormat = "mm/dd/yy"
End With

What do you mean by take the day part? What are you aiming to do?
--

HTH

RP

Thanks JulieD and Bob for your answers... still not working. I can't believe
something so simple is giving me such a hard time. I think I know what the
problem is. Soon as I take the day part ("Friday,") it works (both solutions)
perfect.
I need to find a way to Left Trim to the first ","

I'll keep trying

 

[Guest]

Hi Bob,
I've used your code and the only way that works is when I manualy delete the
day:
"Friday, October 15, 2004 10:38:12"
Once I got "October 15, 2004 10:38:12" the format displays "10/15/04"
That's what I mean if I can left trim to the first comma, It would display
only
"October 15, 2004 10:38:12" and work fine

Thanks in advance for all your help
g

 

[Myrna Larson]

Ah, the problem, then, is that the cell DOESN'T contain an Excel date and time
(which is a number), it contains text:

Dim X as String
Dim i As Long
Dim D As Date

X = Range("E80").Value
i = Instr(x, ",")
D = CDate(Trim$(Mid$(X, i + 1)))

Thanks JulieD and Bob for your answers... still not working. I can't believe
something so simple is giving me such a hard time. I think I know what the
problem is. Soon as I take the day part ("Friday,") it works (both solutions)
perfect.
I need to find a way to Left Trim to the first ","

I'll keep trying

 

[Guest]

Thanks Myrna. Just what I needed! Thanks everybody
g