Posted to microsoft.public.excel.programming
|
|
Impossible Date Formatting Requested?
That's a really handy tip. Thanks Dave.
--
Rob van Gelder - http://www.vangelder.co.nz/excel
"Dave Peterson" wrote in message
...
If you do it manually:
Rightclick|Format|Cells|Number Tab
Custom
mm/dd/yyyy(alt-0010)hh:mm:ss
(alt-0010) means you have to hit and hold the alt key while typing 0010 on
the
numeric keypad--not above the QWERTY keys.)
Also set the cell's format for wordwrap.
It doesn't react to autofitting rows, either.
James Cox wrote:
Lance -
Thanks - that pegged it!
I'm not sure why my attempts to do the same thing via the right-click
menu
item Format Cells and then using a custom format didn't work, but this
sure
does.
Thanks again to everyone - now I get to go encourage my users to dream
up
other more impossible requests! : ) : ) : ) : ) : ) : ) : )
: )
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
|
|
--
Dave Peterson
|