How can I enter 8 digits in excel (eg 19710625) and get it to dis.

[Moira]

I am wondering if I can enter 8 digits in an Excel Cell and have it display
as a date without having to put in a "/" or "-" in between.

Thanks!
Moira

 

[Rich Palarea]

With your cursor in the cell, right click and select Format Cell.

Scroll down to "date" and then select the desired format from the list at
the right. If you desire a format that is not listed, you can create it.

HTH
Rich

 

[Ron Rosenfeld]

I am wondering if I can enter 8 digits in an Excel Cell and have it display
as a date without having to put in a "/" or "-" in between.

Thanks!
Moira

You could use this formula in a helper cell (assumes your entry is mmddyyyy so
today would be 03152005

=DATE(MOD(E1,10^4),INT(E1/10^6),MOD(INT(E1/10^4),100))


--ron

 

[Moira]

Hi Rick!

I have already done that and it doesnt work. I have to either put in a "-"
or a "/" after each number sequence (e.g. 1971/06/25 or 1971-06-25) to get
the date to show as 1971/06/25.

I would like to be able to put in 19710625 and have it show up in the cells
as 1971/06/25
Thanks!
Moiraa

 

[Moira]

Hi Ron!

Would the formula be the same if the format was YYYYMMDD?

Thanks!
Moira

 

[Moira]

Hi Again Ron!

Can you please explain what a "helper cell" is, and can't I just put in a
formula to do this?
Thanks!
Moira

 

[Ron Rosenfeld]

Hi Again Ron!

Can you please explain what a "helper cell" is, and can't I just put in a
formula to do this?
Thanks!
Moira

Just some convenient cell where you enter the formula. It might be the cells
in an adjacent column to your data, or elsewhere.

After you run the formula, you can do Edit/Copy Paste Special/Values over the
original; and then delete the "helper cells" or "helper column". Or not -- you
could just use the cells where the formula is.


--ron

 

[Ron Rosenfeld]

Hi Ron!

Would the formula be the same if the format was YYYYMMDD?

Thanks!
Moira

Look at HELP for the DATE function and you will see that it cannot possibly be
the same.

However, you might be able to use a different approach:

=--(TEXT(A1,"0000\/00\/00"))

and format as a date.


--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

You could use this formula in a helper cell (assumes your entry is mmddyyyy so
today would be 03152005

=DATE(MOD(E1,10^4),INT(E1/10^6),MOD(INT(E1/10^4),100))

If you're going to use formulas in additional ancillary cells, why not

=--TEXT(E1,"0000\-00\-00")

However, easier to just enter these dates in a batch, then select the
range and run the menu command Data > Text to Columns, choose either
Delimited or Fixed Width in step 1 of the wizard, then click Next twice
to advance to step 3 of the wizard, in the Column data format box click
in the Date: radio button and select YMD from the drop-down list, and
click Finish. Not instant gratification, but much easier than the
alternatives.

 

[Gord Dibben]

Moira

If you would like to use VBA code for quick entry of dates/times see Chip
Pearson's site.

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

Or download the QDE add-in from Ron de Bruin's site.

http://www.rondebruin.nl/qde.htm

Ron's add-in would be the easiest if you're not familiar with VBA and macros.


Gord Dibben Excel MVP

 

[Ron Rosenfeld]

However, easier to just enter these dates in a batch, then select the
range and run the menu command Data > Text to Columns, choose either
Delimited or Fixed Width in step 1 of the wizard, then click Next twice
to advance to step 3 of the wizard, in the Column data format box click
in the Date: radio button and select YMD from the drop-down list, and
click Finish. Not instant gratification, but much easier than the
alternatives.

--ron

 

[Ron Rosenfeld]

If you're going to use formulas in additional ancillary cells, why not

=--TEXT(E1,"0000\-00\-00")

I did in a subsequent post.

However, easier to just enter these dates in a batch, then select the
range and run the menu command Data > Text to Columns, choose either
Delimited or Fixed Width in step 1 of the wizard, then click Next twice
to advance to step 3 of the wizard, in the Column data format box click
in the Date: radio button and select YMD from the drop-down list, and
click Finish. Not instant gratification, but much easier than the
alternatives.

That does not work with a mmddyyyy entry format. Had you read my initial post,
you would have noted that I specified that format. It was only in a later post
that the OP mentioned YYYYMMDD as a format she wished to use.

==================================



--ron

 

[Myrna Larson]

Data > Text to Columns .... does not work with a mmddyyyy entry format

I just experimented with this. If the column is formatted as general, and you
enter a date like 03152005, the leading 0 is removed, the cell shows 3152005
(7 digits) and Text to Columns doesn't work.

BUT... if you preformat the column as text before entering the data, or enter
Jan-Sep dates with a leading apostrophe and leading zeroes, Text to Columns
DOES work. i.e. the "trick" is that you have to have 8 digits, with the
leading zero for Jan-Sep.

 

[Ron Rosenfeld]

BUT... if you preformat the column as text before entering the data, or enter
Jan-Sep dates with a leading apostrophe and leading zeroes, Text to Columns
DOES work. i.e. the "trick" is that you have to have 8 digits, with the
leading zero for Jan-Sep.

Interesting. I didn't get that far, stopping after the data entry.

Still, the =--TEXT(...) formula will work even with dropping the leading
zero's.


--ron

 

[Harlan Grove]

That does not work with a mmddyyyy entry format. Had you read my
initial post, you would have noted that I specified that format.
It was only in a later post that the OP mentioned YYYYMMDD as a
format she wished to use.

So it wasn't obvious to you from the *subject* line that the OP's dates were
in yyyymmdd format? Hard to figure that 19710625 was either day 19 in month
71 or month 19 day 71.

 

[Ron Rosenfeld]

So it wasn't obvious to you from the *subject* line that the OP's dates were
in yyyymmdd format? Hard to figure that 19710625 was either day 19 in month
71 or month 19 day 71.

No, I missed the subject line.

As you missed the content of my message.




--ron

 

[Harlan Grove]

Ron Rosenfeld wrote...
....

No, I missed the subject line.

As you missed the content of my message.

Quite so. Silly me, responding to the OP's obvious specs rather than to
irrelevant tangents due to misunderstanding in your response.

BTW, if A1 contained the number 11221987 and A2 contained the string
01011901, if I select A1:A2 and run the menu command Data > Text to
Columns, choose either Delimited or Fixed Width, then advance to step 3
of the wizard and choose Date as the column's data type and select MDY
(yes, different from my earlier response because this time I'm
following your tangent), and click Finish, Excel *DOES* convert the
entries into the expected dates. The reason I used YMD in my previous
response is because I could figure out what the OP wanted by reading
the subject line, and I was more interested in answering the OP's
question than correcting you . . . at that time.

 

[Ron Rosenfeld]

I was more interested in answering the OP's
question than correcting you . . . at that time.

Well, it might have been best to respond to the OP, in that case.
--ron