A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Formatting



 
 
Thread Tools Display Modes
  #31  
Old January 9th, 2006, 01:05 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Date Formatting

I agree with the merit of saving the original data, but my point was that if
it doesn't allow the column width to be reduced, what is the point of having
it wrap to multiple lines, why not just use a simple form of 'dddd dd mmmm
yyyy' which still preserves the data. That is why I never use that
technique, I see absolutely no merit in it. A typical example of a good idea
badly implemented in Excel IMO (... don't get me started on charts and pivot
tables vbg).

Regards

Bob

(remove nothere from email address if mailing direct)

"Roger Govier" wrote in message
...
Hmmm....

I can see the absolute merit of Dave's keeping the date in a format for
use in further calculations (how are we going to do all those SP
formulae otherwise Bobvbg).
I can also see the OP's desire for having a nice formatting.

Me, Id' just have a good old compromise and do both. Have a hidden
column B and write the date value to that cell from my code,
unformatted, and then use that column if I needed to do any
calculations.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
In that case, why would you want the wrap-around?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dave Peterson" wrote in message
...
I'm not sure I'd say nullify. I (still) think that it depends on the
circumstance. If this were a header for a column full of wide

descriptions,
then I wouldn't care at all.


Bob Phillips wrote:

It is good that it keeps the original value, but the fact that you
have

to
allow cell width to cater for the full text string nullifies the

usefulness
of this technique IMO.

Bob

"Dave Peterson" wrote in message
...
One of the nicer things about using format is that the value
stays a

date,
too.
So it makes doing date arithmetic a little easier if you need it

later.



Roger Govier wrote:

Hey, that's quite sneaky Dave.
I would never have thought about entering control characters
into

the
number format.
Very effective.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
You can use format|cells|number tab

Give it a custom format of:
dddd(alt-0010)dd mmmm yyyy

Hit and hold the alt key while typing 0010 on the numeric

keypad--not
above the
QWERTY keys.

And turn on wrap text
format|cells|alignment tab

But be aware that you'll have to adjust the rowheight
yourself
(autofitting
won't work).

And if you make the cell too narrow for the whole string (not

after
wrapping
text), you'll see ###'s.

====
Another format to consider:
dddd* dd mmmm yyyy
This works pretty neat when you widen the column.



Big Rick wrote:

Hello Fellow Excellers.
When I enter a date in a cell and give it a long format.
e.g.

dddd dd
mmmm
yyyy, so that it would read Monday 1 January 2006, then the
wrap

text
facility does not work.
Is there any way to put in an 'alt-enter' type character so
that

I
could
have the Monday on the first line and the rest underneath.
--
Your help is and always has been very much appreciated.
Thanking you in anticipation.
--- --- ---
Big Rick

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson







  #32  
Old January 9th, 2006, 03:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Date Formatting

No other reason than "just because".

Actually, I like this format much better:
dddd* mm/dd/yyyy

But that's just my opinion.

Bob Phillips wrote:

In that case, why would you want the wrap-around?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dave Peterson" wrote in message
...
I'm not sure I'd say nullify. I (still) think that it depends on the
circumstance. If this were a header for a column full of wide

descriptions,
then I wouldn't care at all.


Bob Phillips wrote:

It is good that it keeps the original value, but the fact that you have

to
allow cell width to cater for the full text string nullifies the

usefulness
of this technique IMO.

Bob

"Dave Peterson" wrote in message
...
One of the nicer things about using format is that the value stays a

date,
too.
So it makes doing date arithmetic a little easier if you need it

later.



Roger Govier wrote:

Hey, that's quite sneaky Dave.
I would never have thought about entering control characters into

the
number format.
Very effective.

--
Regards

Roger Govier

"Dave Peterson" wrote in message
...
You can use format|cells|number tab

Give it a custom format of:
dddd(alt-0010)dd mmmm yyyy

Hit and hold the alt key while typing 0010 on the numeric

keypad--not
above the
QWERTY keys.

And turn on wrap text
format|cells|alignment tab

But be aware that you'll have to adjust the rowheight yourself
(autofitting
won't work).

And if you make the cell too narrow for the whole string (not

after
wrapping
text), you'll see ###'s.

====
Another format to consider:
dddd* dd mmmm yyyy
This works pretty neat when you widen the column.



Big Rick wrote:

Hello Fellow Excellers.
When I enter a date in a cell and give it a long format. e.g.

dddd dd
mmmm
yyyy, so that it would read Monday 1 January 2006, then the wrap

text
facility does not work.
Is there any way to put in an 'alt-enter' type character so that

I
could
have the Monday on the first line and the rest underneath.
--
Your help is and always has been very much appreciated.
Thanking you in anticipation.
--- --- ---
Big Rick

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #33  
Old January 9th, 2006, 04:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Date Formatting


"Dave Peterson" wrote in message
...

Actually, I like this format much better:
dddd* mm/dd/yyyy


So do I.


  #34  
Old January 11th, 2006, 07:25 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Date Formatting

I did finally get Bob' code to work by pre formatting the cells. As you can
see from the previous post, I was trying to input the date into cell H1.
Thanks for the suggestions as to why it didnt work. Many thanks to Bob also.

Many Regards
--
Big Rick


"Max" wrote:

.. I did get Rogers code to work, I couldn't with Bobs',


Big Rick (as posted in the other branch),

With Bob's sub implemented properly,

Select and *pre-format* the designated input range: H1:H10
as Custom: dddd dd mmmm yyyy

Then test by inputting some dates within H1:H10

When you press ENTER to commit each input,
Bob's code will return exactly what you're after

(My guess is you either didn't pre-format H1:H10 beforehand, or you might
have tried out the inputs elsewhere .. in A1, A2?? [I dunno]. But don't ask
me why Bob prefers "H1:H10" instead of "A1:A10" as the assumed source input
range g)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--



  #35  
Old January 11th, 2006, 11:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Date Formatting

Glad to hear you got it to work!
And thanks for posting back

As you can see from the previous post,
I was trying to input the date into cell H1 ...


Yes, you did. Overlooked this earlier, apologies
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Big Rick" wrote in message
...
I did finally get Bob' code to work by pre formatting the cells. As you

can
see from the previous post, I was trying to input the date into cell H1.
Thanks for the suggestions as to why it didnt work. Many thanks to Bob

also.

Many Regards
--
Big Rick



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting of date cebubum General Discussion 4 June 28th, 2005 03:27 AM
Date Range and Average F/X Rate David General Discussion 0 June 23rd, 2005 02:26 PM
Problem formatting date values in chart... Private Person Charts and Charting 7 October 14th, 2004 05:00 AM
QDE (Quick Date Entry) Norman Harker Worksheet Functions 37 September 5th, 2004 01:24 AM
QDE (Quick Date Entry) Norman Harker General Discussion 3 September 3rd, 2004 08:00 AM


All times are GMT +1. The time now is 01:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.