Impossible Date Formatting Requested?

  • Thread starter Thread starter James Cox
  • Start date Start date
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
 
Put it in 2 separate cells, one above the other.
It's not like you're going to run out of rows...
 
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
 
Hi James

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


--
XL2002
Regards

William

(e-mail address removed)

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

(e-mail address removed)

| 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
| >
| > (e-mail address removed)
| >
| > | > | 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
 
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

(e-mail address removed)

| 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
| >
| > (e-mail address removed)
| >
| > | > | 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
| > | >
| > | > (e-mail address removed)
| > | >
| > | > | > | > | 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
| > | > |
| > | > |
| > | >
| > | >
| > |
| > |
| >
| >
|
|
 
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
 
Hide a column, assign value in another column
=TEXT(A35,"mm/dd/yy" & CHAR(10) & "hh:mm:ss")
 
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
 
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtracting AD and/or BC dates 21
Find then highlight in yellow 6
date format cell, to text format 2
Date format. 1
Date formatting in VBA 1
Excel is changing date format incorrectly 7
Dates 2
Date match problem 2

Back
Top