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

Put it in 2 separate cells, one above the other.
It's not like you're going to run out of rows...

"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

Thanks for the attempt, Stephen, but it's not quite that simple - the dates
are coming from a third-party add-in that uses array formulas (and calls
into a process database) that I can't hack into, so it really has to be what
I was asking about - a single-cell date display format solution.

Sorry for not making that clearer in my post : (

James


"Stephen Bye" <. wrote in message
...
Put it in 2 separate cells, one above the other.
It's not like you're going to run out of rows...

"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

Hide a column, assign value in another column
=TEXT(A35,"mm/dd/yy" & CHAR(10) & "hh:mm:ss")

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"James Cox" wrote in message ...
Thanks for the attempt, Stephen, but it's not quite that simple - the dates
are coming from a third-party add-in that uses array formulas (and calls
into a process database) that I can't hack into, so it really has to be what
I was asking about - a single-cell date display format solution.

Sorry for not making that clearer in my post : (

James


"Stephen Bye" <. wrote in message
...
Put it in 2 separate cells, one above the other.
It's not like you're going to run out of rows...

"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

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
|
|

William -

That looked like it had promise, but I don't get the option to set the cell
contents - just to format what's already there.

Also, a check on what Excel thinks is in the cell - using the following in
the VBA editor's Immediate pane

?isdate(range("a1").Value)

gives a value of "False" so it's no longer a date that Excel can use...

Thanks for giving it a shot!

James


"William" wrote in message
...
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
|
|

James

Your original post required a cell to be split showing the date and time. My
post attempted to address that issue. Are you now saying there are further
requirements? If so, please post them.

--
XL2002
Regards

William



"James Cox" wrote in message
...
| William -
|
| That looked like it had promise, but I don't get the option to set the
cell
| contents - just to format what's already there.
|
| Also, a check on what Excel thinks is in the cell - using the following in
| the VBA editor's Immediate pane
|
| ?isdate(range("a1").Value)
|
| gives a value of "False" so it's no longer a date that Excel can use...
|
| Thanks for giving it a shot!
|
| James
|
|
| "William" wrote in message
| ...
| 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
| |
| |
|
|
|
|

Well, it's obvious that I'm not "The Great Communicator" : (

The part about my not getting to set the contents of the cell was definitely
not expressed clearly (if at all) in my original post but the reqirement
that Excel still consider the result as a date I thought was pretty well
stated -

<snip
doing a date-to-text conversion and then using line wrap formatting of the
cell would "work" - EXCEPT that the date has to stay an Excel date,
not a text string.
<snip

Sorry for any misunderstandings - I'm not trying to offend anyone!

James

"William" wrote in message
...
James

Your original post required a cell to be split showing the date and time.
My
post attempted to address that issue. Are you now saying there are further
requirements? If so, please post them.

--
XL2002
Regards

William



"James Cox" wrote in message
...
| William -
|
| That looked like it had promise, but I don't get the option to set the
cell
| contents - just to format what's already there.
|
| Also, a check on what Excel thinks is in the cell - using the following
in
| the VBA editor's Immediate pane
|
| ?isdate(range("a1").Value)
|
| gives a value of "False" so it's no longer a date that Excel can use...
|
| Thanks for giving it a shot!
|
| James
|
|
| "William" wrote in message
| ...
| 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
| |
| |
|
|
|
|

Apologies

You're right.

As far as I know, but I could well be wrong, I don't think you can "wrap" a
number in Excel. As a date is a number, you will not be able to wrap any
result in the cell unless it is text.

--
XL2002
Regards

William



"James Cox" wrote in message
...
| Well, it's obvious that I'm not "The Great Communicator" : (
|
| The part about my not getting to set the contents of the cell was
definitely
| not expressed clearly (if at all) in my original post but the reqirement
| that Excel still consider the result as a date I thought was pretty well
| stated -
|
| <snip
| doing a date-to-text conversion and then using line wrap formatting of the
| cell would "work" - EXCEPT that the date has to stay an Excel date,
| not a text string.
| <snip
|
| Sorry for any misunderstandings - I'm not trying to offend anyone!
|
| James
|
| "William" wrote in message
| ...
| James
|
| Your original post required a cell to be split showing the date and
time.
| My
| post attempted to address that issue. Are you now saying there are
further
| requirements? If so, please post them.
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "James Cox" wrote in message
| ...
| | William -
| |
| | That looked like it had promise, but I don't get the option to set the
| cell
| | contents - just to format what's already there.
| |
| | Also, a check on what Excel thinks is in the cell - using the
following
| in
| | the VBA editor's Immediate pane
| |
| | ?isdate(range("a1").Value)
| |
| | gives a value of "False" so it's no longer a date that Excel can
use...
| |
| | Thanks for giving it a shot!
| |
| | James
| |
| |
| | "William" wrote in message
| | ...
| | 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
| | |
| | |
| |
| |
| |
| |
|
|
|
|

will this do?
Selection.NumberFormat = "dd/mmm/yyyy" & vbLf & "hh:mm:ss"


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"William" wrote:

James

Your original post required a cell to be split showing the date and
time. My post attempted to address that issue. Are you now saying
there are further requirements? If so, please post them.

strange
i post it first.. somebody else gets a thank you...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepITcool wrote:

will this do?
Selection.NumberFormat = "dd/mmm/yyyy" & vbLf & "hh:mm:ss"


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"William" wrote:

James

Your original post required a cell to be split showing the date and
time. My post attempted to address that issue. Are you now saying
there are further requirements? If so, please post them.