Date format 050180 to 05/01/80
 

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

A1: 050180
A2: =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)
--
John C


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

Are you sure that you've got the parameters of the date function in the
right order, John?
Wouldn't it be =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
A1: 050180
A2: =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)
--
John C


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

Thank you Thank you Thank you!

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

One other question...once I converted all my current data to that format and
set all the following cells to have the date format of mm/dd/yy, when I then
entered a date, for example 010101 it came out as 08/27/27. What happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

You would need an event macro to change numbers into date like that, to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that format
and
set all the following cells to have the date format of mm/dd/yy, when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

Thank you. I will try that. So, in the future if I want my data to appear
as mm/dd/yy, how do I do that? I tried opening a new worksheet and
formatting the cells for that format and then tried entering the data and it
didn't work. I ask as I have more data to add to this worksheet...

"Peo Sjoblom" wrote:

You would need an event macro to change numbers into date like that, to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that format
and
set all the following cells to have the date format of mm/dd/yy, when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

If you don't want to use the macro, then you'll have to type in your entry so
that excel sees it as a date:

01/01/2001
(I like 4 digit years!)

LauraNH wrote:

Thank you. I will try that. So, in the future if I want my data to appear
as mm/dd/yy, how do I do that? I tried opening a new worksheet and
formatting the cells for that format and then tried entering the data and it
didn't work. I ask as I have more data to add to this worksheet...

"Peo Sjoblom" wrote:

You would need an event macro to change numbers into date like that, to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that format
and
set all the following cells to have the date format of mm/dd/yy, when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!




--

Dave Peterson

Date format 050180 to 05/01/80
 

I must be doing something wrong because neither of those suggestions worked.

"David Biddulph" wrote:

Are you sure that you've got the parameters of the date function in the
right order, John?
Wouldn't it be =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))
--
David Biddulph

"John C" <johnc@stateofdenial wrote in message
...
A1: 050180
A2: =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)
--
John C


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

Do you mean enter the data as 01012001 or type it in myself with the dashes?
Please forgive my ignorance.

"Dave Peterson" wrote:

If you don't want to use the macro, then you'll have to type in your entry so
that excel sees it as a date:

01/01/2001
(I like 4 digit years!)

LauraNH wrote:

Thank you. I will try that. So, in the future if I want my data to appear
as mm/dd/yy, how do I do that? I tried opening a new worksheet and
formatting the cells for that format and then tried entering the data and it
didn't work. I ask as I have more data to add to this worksheet...

"Peo Sjoblom" wrote:

You would need an event macro to change numbers into date like that, to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that format
and
set all the following cells to have the date format of mm/dd/yy, when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!




--

Dave Peterson

Date format 050180 to 05/01/80
 

Nope. You need the dashes or slashes.

LauraNH wrote:

Do you mean enter the data as 01012001 or type it in myself with the dashes?
Please forgive my ignorance.

"Dave Peterson" wrote:

If you don't want to use the macro, then you'll have to type in your entry so
that excel sees it as a date:

01/01/2001
(I like 4 digit years!)

LauraNH wrote:

Thank you. I will try that. So, in the future if I want my data to appear
as mm/dd/yy, how do I do that? I tried opening a new worksheet and
formatting the cells for that format and then tried entering the data and it
didn't work. I ask as I have more data to add to this worksheet...

"Peo Sjoblom" wrote:

You would need an event macro to change numbers into date like that, to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that format
and
set all the following cells to have the date format of mm/dd/yy, when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!




--

Dave Peterson


--

Dave Peterson

Date format 050180 to 05/01/80
 

It doesn't help that you merely said "neither of those suggestions worked".
We're more likely to be able to help you if you tell us what result you got
from what formula with what input data, and what result you expected.

In the absence of the data, I guess that you don't have 050180 in your input
cell, but instead you have 50180, possibly formatted as 000000.

If so, you may need to change your
=DATE(RIGHT(A2,2),LEFT(A2,2),MID(A2,3,2)) to
=DATE(RIGHT(TEXT(A2,"000000"),2),LEFT(TEXT(A2,"000 000"),2),MID(TEXT(A2,"000000"),3,2))--David Biddulph"LauraNH" wrote in ...I must be doing something wrong because neither of those suggestionsworked. "David Biddulph" wrote: Are you sure that you've got the parameters of the date function in the right order, John? Wouldn't it be =DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2)) -- David Biddulph "John C" <johnc@stateofdenial wrote in message ... A1: 050180 A2: =DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2) -- John C "LauraNH" wrote: If data is entered as mmddyy, how do I get it to change to mm/dd/yy?I have tried formatting the cell to no avail. HELP!

Date format 050180 to 05/01/80
 

If you just type it in as a number, such as 01012001, then it *won't* be
recognised as a date (unless you use a macro as advised).
You could use 01/01/2001, but in that format there is scope for confusion
between dd/mm/yyyy and mm/dd/yyyy, so you need to be sure what your Windows
Regional Settings are (not just the Excel cell format).
If you are not sure of your settings, there is less scope for confusion if
the input is of a form such as 2001-01-01.
--
David Biddulph

"LauraNH" wrote in message
...
Do you mean enter the data as 01012001 or type it in myself with the
dashes?
Please forgive my ignorance.

"Dave Peterson" wrote:

If you don't want to use the macro, then you'll have to type in your
entry so
that excel sees it as a date:

01/01/2001
(I like 4 digit years!)

LauraNH wrote:

Thank you. I will try that. So, in the future if I want my data to
appear
as mm/dd/yy, how do I do that? I tried opening a new worksheet and
formatting the cells for that format and then tried entering the data
and it
didn't work. I ask as I have more data to add to this worksheet...

"Peo Sjoblom" wrote:

You would need an event macro to change numbers into date like that,
to
Excel
010101 = 10,101 meaning 10,101 days since Jan 0 1900 thus Aug 27 1927

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

how to install macros


http://www.mvps.org/dmcritchie/excel/install.htm

--


Regards,


Peo Sjoblom

"LauraNH" wrote in message
...
One other question...once I converted all my current data to that
format
and
set all the following cells to have the date format of mm/dd/yy,
when I
then
entered a date, for example 010101 it came out as 08/27/27. What
happened?

"JMB" wrote:

First, you need to convert the data to an actual date. Try
clicking
Data/Text To Columns. Select Delimited, click Next, ensure all
checkboxes
under the Delimiters section are unchecked, click next, under Data
Format
section select Date and MDY, click Finish.

Now XL should have converted your data to a date. You should be
able to
change it to a different date format as needed.


"LauraNH" wrote:

If data is entered as mmddyy, how do I get it to change to
mm/dd/yy? I
have
tried formatting the cell to no avail. HELP!




--

Dave Peterson