changing date format

[Julie]

I have spreadsheets with dates I need to convert in order to send info. to
customer electronically.

Current spreadsheet shows 09/20/08 or September 20, 2008
Need it to show 080920 with no slashes

Can anybody help me?

 

[ryguy7272]

Right-click on cell, Format Cells > Custom > Type = yymmdd

Regards,
Ryan--

 

[Julie]

Wow - easy - but I hadn't figured it out. Thank-you so much - you saved me
a ton of time!!!

 

[Gord Dibben]

That won't "convert" the dates, just display them differently.

The underlying value will still be 09/20/08 or September 20, 2008

Do you need them actually "converted" to a text value of 080920 ?

="0"&(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)


Gord Dibben MS Excel MVP

 

[Kathy]

I have a similar question...how can you set a new Custom date in the cell
format application?

 

[Tyro]

Select the date you want to format. The Ctrl+1. Select Numbers tab. Select
custom. In the "Type:" box enter the format you want - e.g. m/d/yyyy or
select one of the existing formats. See Excel help for the possible formats
you can use.

Tyro

 

[Gord Dibben]

Format>Cell>Number>Custom.

Enter your Custom Format.

Save the Workbook.

The custom is saved with that workbook only and is not a global custom.

To make it a global custom format you would have to create a workbook template
upon which all new workbooks are based.

Open a new workbook. Customize cells as you wish with your number format.

File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).

Alternative................create a small macro saved in your Personal.xls and
assign it to a button.


Gord

 

[Rick Rothstein \(MVP - VB\)]

That won't "convert" the dates, just display them differently.

The underlying value will still be 09/20/08 or September 20, 2008

Do you need them actually "converted" to a text value of 080920 ?

="0"&(YEAR(A1)-2000)*10000+MONTH(A1)*100+DAY(A1)

Or even like this..

=TEXT(A1,"yymmdd")

Rick

 

[Teethless mama]

Try this:

TEXT(A1,"yymmdd")

 

[Rick Rothstein \(MVP - VB\)]

Now why didn't I think of that? <vbg>

Rick

Try this:

TEXT(A1,"yymmdd")

 

[util]

I have just the opposite issue - I need:
08242007 converted to 08/24/07
Please help - I'm getting ############# error not due to column size..
Thanks

 

[Fred Smith]

First, don't hijack threads. If you have a new problem post it in a new
thread. You'll get a much better response.

Second, if you need it converted to a date, then just convert it. Try:

=date(right(a1,4),left(a1,2),mid(a1,3,2))

Regards,
Fred.

 

[Gord Dibben]

Data>Text to Columns>Next>Next>Column Data Format>Date>MDY and Finish.


Gord Dibben MS Excel MVP