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

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.

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

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

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

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

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

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.

Thanks <vbg.

keepITcool wrote:

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.



--

Dave Peterson