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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Date Formatting
.. It worked fine in my tests.
And it worked fine for me over here, too g Big Rick: 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 -- |
#22
|
|||
|
|||
Date Formatting
A little "OT", sorry ..
Am trying out Dave's suggestion, but think I've forgotten how to activate/deactivate the numeric keypad on a laptop Thanks .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#23
|
|||
|
|||
Date Formatting
.. 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 -- |
#24
|
|||
|
|||
Date Formatting
Hi Max
You should have a button marked Fn and the word Numlock in coloured type at the base of a key somewhere. Press both, and it should activate M as 0, JKL as 123, UIO as 456 with the number keys 789 being 789. -- Regards Roger Govier "Max" wrote in message ... A little "OT", sorry .. Am trying out Dave's suggestion, but think I've forgotten how to activate/deactivate the numeric keypad on a laptop Thanks .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#25
|
|||
|
|||
Date Formatting
My Dell has small colored numbers on the letter keys, matching Roger's
scenario, where all that's necessary is to hold the Fn key (right of the left Ctrl key) and the Alt key, and then use those colored numbers. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roger Govier" wrote in message ... Hi Max You should have a button marked Fn and the word Numlock in coloured type at the base of a key somewhere. Press both, and it should activate M as 0, JKL as 123, UIO as 456 with the number keys 789 being 789. -- Regards Roger Govier "Max" wrote in message ... A little "OT", sorry .. Am trying out Dave's suggestion, but think I've forgotten how to activate/deactivate the numeric keypad on a laptop Thanks .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#26
|
|||
|
|||
Date Formatting
Thanks, Roger & RD ! With your help, think I got it g.
For my m/c (IBM), I had to press Shift + ScrLk to toggle (the tiny "Numlk" appears above "ScrLk" in white) And the numeric* keypad numbers appear in white too (no color), albeit in "subscript" form. *as explained in Roger's response: .. M as 0, JKL as 123, UIO as 456 with the number keys 789 being 789. Cheers -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#27
|
|||
|
|||
Date Formatting
"Max" wrote in message ... (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) A1:A10 is boring vbg |
#28
|
|||
|
|||
Date Formatting
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 |
#29
|
|||
|
|||
Date Formatting
Hi Roger,
I look forward to that. Tanglefoot, Wadworths 6X, topped off with a decent pint of Director's if we can find it. We also have a local Ringwood micro-brewery which does a nice ale. Bob "Roger Govier" wrote in message ... Hi Bob Then before too long, I must travel down to your neck of the woods and enjoy a pint of Badger with you. -- Regards Roger Govier "Bob Phillips" wrote in message ... Roger, You know that all Brits can drink! The world is astounded at our capacity. Bob "Roger Govier" wrote in message ... Hi Big Rick If anyone of you are ever in England, then the drinks are on me ! Be careful there, Bob is in Dorset, I am in Wales (but often across the border) Don't know about Bob, but I can drink quite a lot of beer!! vbg -- Regards Roger Govier "Big Rick" wrote in message ... Dear Bob, Max, Dave & Roger I am forever grateful for all your time, effort and patience for someone who has to have something drummed in before it sticks in my mind. I did get Rogers code to work, I couldn't with Bobs', although I sure that it is because a complete amateur is trying. If anyone of you are ever in England, then the drinks are on me ! Regards -- Big Rick "Dave Peterson" wrote: 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 |
#30
|
|||
|
|||
Date Formatting
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 |
Thread Tools | |
Display Modes | |
|
|
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 |