Excel Date Format Question 1

[Sopman]

I have about 300 entries to put into a spreadsheet with differents dates. I would like to enter the date like 031005 and have it be displayed as 03/10/05. I tried Format-Cell, but it converts it to a different date. Is there a way to do this?

Sopman

 

[AnotherHiggins]

If you already have the dates entered that way, you can convert it to a date, but there is no way to just type 031005 into a cell and have it know that you want to enter a date. You have to use a divider like "/" or "-".

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[fkegley]

Format Cells Custom select the date format that comes closest to what you want, edit it.


Frank kegley
fkegley@hotmail.com

 

[SkipVought]

Hi,

In Microsoft Applications 031005 is NOT a real date -- is's just a string of digits. A date is just a number, like today is 38462, that you can FORMAT any number of ways using Format/Cell/Number tab -- some date format.

To CONVERT, you must stuff the year, month and day into the DATE function, assuming that your digits are mmddyy, and if your digits are in A1...
[tt]
=DATE(if(right(A1,2)<"30",2000,1900)+right(A1,2),left(A1,2),mid(A1,3,2))
[/tt]



Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Sopman]

fkegley, it displays it as a different date.

 

[SkipVought]

That's because it assumes that 31005 is a DateSerial value which format to 11/19/1984.


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[AnotherHiggins]

Frank,

MM/DD/YY is a standard date format listed in the format dialog box, but that won't help in this case.

I would like to enter the date like 031005 and have it be displayed as 03/10/05

(empasis mine)

It doesn't matter how a cell is formatted, typing in "031005" is going to be interpretted as a number by Excel. Formatting the cell as MM/DD/YY will only cause the cell to display 11/19/84 (31,005 days after 1/1/1900).

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[Sopman]

Skip, where do I put the formula?

Sopman

 

[SkipVought]

in an empty column in that row.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[AnotherHiggins]

From your original post you indicate that you haven't entered the dates yet. Is an extra two keystrokes really going to be that much harder than using a formula?

I have about 300 entries to put into a spreadsheet with differents dates.

Are you going to manually type in the dates? Are the dates in ascending order? Do several of the entries have the same date?

If you plan to manually enter 300 dates, there is almost definitely a faster, better way to do it.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[fredericofonseca]

And if you are going to manually enter the information you bay be better off using a FORM to enter the information.

That way you can enter the dates as you wish, and then within code you format it as a date before placing it on the cell.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sopman]

We are entering random names of applicates, so there is no order. I said 300, it's more like 550 and I was just trying to make it easier.

Skip, sorry but still not clear on the formula. All the dates are in Column C12:C567.

Sopman

 

[SkipVought]

[tt]
=DATE(if(right(C12,2)<"30",2000,1900)+right(C12,2),left(A1,2),mid(C12,3,2))
[/tt]
and COPY/PASTE down to row 567

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}