Impossible Date Formatting Requested?

J

James Cox

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
 
S

Stephen Bye

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

James Cox

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
 
W

William

Hi James

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


--
XL2002
Regards

William

[email protected]

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

James Cox

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
 
W

William

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

[email protected]

| 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
|
|
| | > Hi James
| >
| > Sub DateAndTime()
| > Range("A1") = Format(Now, "dd mmm yy") & _
| > Chr(10) & Format(Now, "hh mm ss")
| > End Sub
| >
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > [email protected]
| >
| > | > | 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
| > |
| > |
| >
| >
|
|
 
J

James Cox

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
 
W

William

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

[email protected]

| 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
|
| | > 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
| >
| > [email protected]
| >
| > | > | 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
| > |
| > |
| > | | > | > Hi James
| > | >
| > | > Sub DateAndTime()
| > | > Range("A1") = Format(Now, "dd mmm yy") & _
| > | > Chr(10) & Format(Now, "hh mm ss")
| > | > End Sub
| > | >
| > | >
| > | > --
| > | > XL2002
| > | > Regards
| > | >
| > | > William
| > | >
| > | > [email protected]
| > | >
| > | > | > | > | 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
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
 
J

James Cox

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
 
D

David McRitchie

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

James Cox

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
 
D

Dave Peterson

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