If typed 12805 then 12/8/05
 

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.

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

--

Vasant




"Steved" wrote in message
...
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.

Steved wrote:
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.

http://www.cpearson.com/excel/DateTimeEntry.htm

it takes VBA to do it


Regards,

Peo Sjoblom

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.

"Peo Sjoblom" wrote:

Steved wrote:
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.

http://www.cpearson.com/excel/DateTimeEntry.htm

it takes VBA to do it


Regards,

Peo Sjoblom

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)

"Vasant Nanavati" wrote:

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

--

Vasant




"Steved" wrote in message
...
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.

"Steved" wrote in message
...

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.


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.

"Steved" wrote...
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)

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.

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.


"Alan" wrote:

"Steved" wrote in message
...

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.


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.

"Steved" wrote in message
...

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.


Hi Steved,

How would you enter 1 Dec 2005?

Alan.

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.

"Alan" wrote:

"Steved" wrote in message
...

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.


Hi Steved,

How would you enter 1 Dec 2005?

Alan.

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.

"Harlan Grove" wrote:

"Steved" wrote...
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)

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.

"Steved" wrote in message
...

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.


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.

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.


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)

wrote in message
ups.com...

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)


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.

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

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

Hope that helps

michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=467020

Hello from Steved

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

Cheers.

"michaelas" wrote:


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

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

Hope that helps

michaelas


--
michaelas
------------------------------------------------------------------------
michaelas's Profile: http://www.excelforum.com/member.php...o&userid=26573
View this thread: http://www.excelforum.com/showthread...hreadid=467020