shop-talk
[Top] [All Lists]

[Shop-talk] OT- Excel help, (before I shoot my computer)!

Subject: [Shop-talk] OT- Excel help, (before I shoot my computer)!
From: eric at megageek.com (eric at megageek.com)
Date: Wed, 14 Mar 2012 22:44:32 -0400
The dates are being manually entered.  The problem is that with the format 
of dd/mm/yy, when the form is corrected, updated or modified, anyone will 
put it in in that format, and it will get reversed and the data will be 
compromised.

Stuart's recommendation works, but I'm afraid as the file moves from 
computer to computer, the date format will be corrupted.

I just don't understand why Excel HAS to change the data as it is 
inputted.  There should be a way to turn it off on a form.

(FWIW, it seems that an asterisk in front of the cell format mask prevents 
this, BUT, the asterisk doesn't work in custom formats, and there is no US 
format that is *dd/mm/yy.)

Anyone know any MS knuckleheads I can strangle?

Eric P
"Be as beneficent as the sun or the sea, but if your rights as a rational 
being are trenched on, die on the first inch of your territory." Ralph 
Waldo Emerson 




Ian McFetridge <shop-talk2 at mcfetridge.org> 
03/14/2012 17:22

To
eric at megageek.com
cc
shop-talk at autox.team.net
Subject
Re: [Shop-talk] OT- Excel help, (before I shoot my computer)!






As you said, the custom format can fix the display problem.
I'm not sure I follow the data-entry problem.  Are you reading dates off 
of forms and manually typing them in one at a time?  If so, you are trying 
to avoid having to reverse the day and month as you type?  You could work 
around this by having a column for day, month, year, then in another 
column use the date() function to combine the day, month, year in the 
proper order.  
Or, are you pasting in a bunch of dates in UK format?

- Ian

On Wed, Mar 14, 2012 at 3:44 PM, <eric at megageek.com> wrote:
Sorry about the off topic post, but my back is against the wall and I
don't know where else to turn.

Here is the issue...

I have an excel spreadsheet that has to have a date cell in the format
dd/mm/yy.  No problem. I set the language to English "UK" and I can select
that format.

Now the problem...

If I enter a date in that format, Excel flips it.  (Ex. If I type in
"03/09/06"  Excel will translate it to "09/03/06" when I move to another
cell.)

If I use a date like "15/03/06" it will not move the date, BUT,  I can't
run formulas against the cell (which is something I need to do.)

It seems like excel will only except me typing in a date in the format of
month-day-year no matter what.  Since the form I get the dates from isn't
like this, it will be way too confusing to continue doing that format.

I've tried creating a custom date cell format and it does the same thing.

Note:  this is excel 2003 version on an XP machine.  But it also does this
on Vista machine with Excel 2007.

HELP!!!!

Thanks in advance.

Shop content, I can't go work in the shop until I finish this spreadsheet!

Eric P
"Be as beneficent as the sun or the sea, but if your rights as a rational
being are trenched on, die on the first inch of your territory." Ralph
Waldo Emerson
_______________________________________________

Shop-talk at autox.team.net
Donate: http://www.team.net/donate.html
Suggested annual donation  $12.96
Archive: http://www.team.net/archive
Forums: http://www.team.net/forums
Unsubscribe/Manage: 
http://autox.team.net/mailman/options/shop-talk/shop-talk2 at mcfetridge.org

<Prev in Thread] Current Thread [Next in Thread>