Just for completeness, I need to share that while this does what my user
said he wanted, it may not give him what he REALLY wanted (anyone else work
with folks like that? : )

It stacks the date perfectly, but if the column the date is in is made just
a bit more narrow, Excel still reverts to the ###### display. So, if his
real desire was to have the hh:mm:ss displayed but not to use as much
spreadsheet width, he's out of luck. I suppose he'll mention it to me if
that's the case! : )

James

"lance" wrote in message
...
or

Sub DateAndTime()
With Selection
.NumberFormat = "dd mmm yy" & Chr(10) & "hh:mm:ss"
.HorizontalAlignment = xlCenter
.WrapText = True
End With
End Sub

If you want to maintain the entry as a date


"William" wrote:

Hi James

Sub DateAndTime()
Range("A1") = Format(Now, "dd mmm yy") & _
Chr(10) & Format(Now, "hh mm ss")
End Sub


--
XL2002
Regards

William



"James Cox" wrote in message
...
| Dontcha just love users? : )
|
| I've got one that wants to display Excel dates in
|
| 03-Jul-04 09:14:22
|
| format, but wants the date and time "stacked" in a single cell, as in
|
| 03-Jul-04
| 09:14:22
|
| doing a date-to-text conversion and then using line wrap formatting on
the
| cell would "work" - EXCEPT that the date has to stay an Excel date,
not a
| text string.
|
| Given Excel's "feature" of displaying dates as ######'s if the cell is
not
| wide enough, I can't get any leverage on a way to do what he wants. I
had
| thought that the ability to embed text into date (and number formats)
might
| be a way to do this, but I can't find a way to get an active CR-LF
between
| the halves of the datetime.
|
| Anyone have any additional ideas on how to do this?
|
| James
|
|