If typed 12805 then 12/8/05

S

Steved

Hello from Steved

If typed 12805 in cell A1 then the value in the cell A1 will be 12/8/05

in Numbers , Custom can I put something like d/m/yy to do above please.

Thankyou.
 
V

Vasant Nanavati

That would be hard to do. How does Excel know whether you mean 1/28/05 (US)
or 12/8/05 (US or non-US)?
 
S

Steved

Hello Peo from Steved

Thankyou I am putting a project together for a Pocket Computer
So i will just have to thinlk off something else.

Thanks for your time.
 
S

Steved

Hello Vasant from Steved

So in other words I cannot do something like "##/#/##" and it reconises it
as text, this way I would get around (US or non-US)
 
A

Alan

Steved said:
Hello from Steved

If typed 12805 in cell A1 then the value in the cell A1 will be
12/8/05

in Numbers , Custom can I put something like d/m/yy to do above
please.

Thankyou.
Click to expand...

What would 11205 mean?

11 Feb 2005

OR

1 Dec 2005

??

The problem is that the format is ambiguous even if you define it
using a standard Day - Month - Year format.

The only way I can see to get around it is to require double digit
entry for the month *at least*.

That way you can specifiy that the last two digits are the year, the
third and fourth digits fom the right are the month, and anything else
in front of that is the day.

HTH,

Alan.
 
H

Harlan Grove

So in other words I cannot do something like "##/#/##" and it
reconises it as text, this way I would get around (US or non-US)
Click to expand...

You can make a cell *appear* as ##/#/## by using the custom number format
##\/#\/##. However, that would screw up entries like 33105, which should
only be 3/31/05 (since 33/1/05 isn't valid in either mm/dd or dd/mm date
formats), and 111105, which would appear as 111/1/05 (also invalid under any
date formatting scheme).

You want to do something that's unavoidably ambiguous, and it'll be
impossible to do consistently correctly.

So, no, you can't do what you want to do unless you accept that it'll
*always* screw up dates with 1-digit month number and 2-digit day number
(US) or 2-digit month number and 1-digit day nimber (non-US), and it'll
screw up *EVERY* date with 2-digit month and 2-digit day numbers.

The only way to enter fully nonambiguous dates is with 8 digits. If you
adopt a 2-digit year number convention, then the only way to enter
nonambiguous dates is with 6 digits. You can't use fewer without problems.
 
S

Steved

Hello Alan from Steved

taking your example 11205

Yes it is a date but for my purpose I want to turn it into 11/2/05
using custom _-$* #,##0_-;-$* #,##0_-;_-$* "-"_-;_-@_-
this turn into $11,205 now if I can use something like this
to turn 11205 into 11/2/05 i would be happy or is it not possible.

Thankyou.
 
A

Alan

Steved said:
Hello Alan from Steved

taking your example 11205

Yes it is a date but for my purpose I want to turn it into 11/2/05
using custom _-$* #,##0_-;-$* #,##0_-;_-$* "-"_-;_-@_-
this turn into $11,205 now if I can use something like this
to turn 11205 into 11/2/05 i would be happy or is it not possible.

Thankyou.
Click to expand...

Hi Steved,

How would you enter 1 Dec 2005?

Alan.
 
S

Steved

Hello Alan

Yes I conceed Yes I must admit I never thought about it 1 Dec 2005

I have decided to take advice from you all and do it another way.

What that is probably back to desktop Pc and use vba

The objective off this exercise was to allow technology do the the work on
behalf of a Pocket computer.

Cheers.
 
S

Steved

Hello Harlan

Thanks for your straight forward explanation

The objective off this exercise was for technology to do all the work on
behalf of a Pocket Computer which supports excel in a limited form.

Thankyou.
 
A

Alan

Steved said:
Hello Alan

Yes I conceed Yes I must admit I never thought about it 1 Dec 2005

I have decided to take advice from you all and do it another way.

What that is probably back to desktop Pc and use vba

The objective off this exercise was to allow technology do the the
work on behalf of a Pocket computer.

Cheers.
Click to expand...

You can still do that, but you have to define an unambigous format for
the dates you enter.

As per my previous post, I think that if you define that the date must
be entered using the following convention you will be okay (this is
one example only - there are many alternative conventions you could
choose):

1) It must be entered Day - Month - Year all numeric digits with no
spaces or other characters

2) The month and year must both be exactly two digits


I think that does it. If the entry is five digits then you must have:

DMMYY

If the entry is six chars then you must have:

DDMMYY

That could then be parsed to either D/MM/YY or DD/MM/YY as required.

If the entry has less than 5 digits or more than 6 then it is invalid
according to the convention defined. Obviously there are five and six
digit entries that are also invalid such as 99999.

HTH,

Alan.
 
S

sandved

using a standard Day - Month - Year format.
You can still do that, but you have to define an unambigous format for
the dates you enter.
Click to expand...


The standard (and non ambigious) way to give a date is year - month -
day (YYYYMMDD or YYYY-MM-DD) as defined in ISO 8601 (See a good
explanation on http://www.cl.cam.ac.uk/~mgk25/iso-time.html and the
Wiki-article at http://en.wikipedia.org/wiki/ISO_8601)

This standard is even adopted by the US (ANSI X3.30-1985(R1991) and
NIST FIPS 4-1 -> http://www.qsl.net/g1smd/isoimp.htm)
 
A

Alan

The standard (and non ambigious) way to give a date is year - month -
day (YYYYMMDD or YYYY-MM-DD) as defined in ISO 8601 (See a good
explanation on http://www.cl.cam.ac.uk/~mgk25/iso-time.html and the
Wiki-article at http://en.wikipedia.org/wiki/ISO_8601)

This standard is even adopted by the US (ANSI X3.30-1985(R1991) and
NIST FIPS 4-1 -> http://www.qsl.net/g1smd/isoimp.htm)
Click to expand...

Indeed, but you are a *long* way off that with what you wanted.

Moreover, the point I was making is that you can choose *any*
unambiguous format for your own purposes, but the one you chose up
front is, unfortunately, not so.

Alan.
 
M

michaelas

steved
I may be misinterpreting your intention, but you can go t
Format/Cells/Numbers/Custom and select d-mmm-yy (in WIN XP). After tha
if you simply type 12/8 it will read out as 8-Dec-05. The defaul
setting for Excel comes from the control panel to read the mont
first.

Alernatively, you can try this:
Control Panel/Regional Settings/Customize/Click Date Tab/set short dat
format to dd-mmm-yyyy

Hope that helps

michaela
 
S

Steved

Hello from Steved

I thankyou all and I will try out the examples that I have be given

Cheers.
 
Top