Get Excell for mac 2011 to convert number into day and date

[drffc]

Hi!

I'm looking to be able to enter 61113 (for Tuesday June 11, 2103)
and have Excel for Mac 2011 convert that to Tue 06/11/13
in each cell after I enter 61113 and tab to the next cell.

Entering a zero before the 6 should be pretty easy, as is entering
the slashes between 6 and 11, and again between 11 and 13.

But the tricky thing may be in getting Excel calculate that
June 11, 2013 was a Tuesday.

Any suggestions?

Many thanks!

[Sam Capricci]

well this will give you the date... =DATEVALUE(LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2)) and if you format it correctly you can get it to look the way you want.

[FDibbins]

Try this...

	D	E
5	61113	Tuesday, June 11, 2013
6	161113	Friday, April 11, 2014

E5=DATE(2000+RIGHT(D5,2),LEFT(D5,IF(LEN(D5)=5,1,2)),MID(D5,IF(LEN(D5)=5,2,3),2))
copied down
then format to show the day as well as the date

[Sam Capricci]

so this will cover two different scenarios - 5 digit or 6 digit values ... =IF(LEN(A1)=5,DATEVALUE(LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2)),DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)))

[Tony Valko]

To do what you want will require VBA.

Does Excel for Mac 2011 support VBA?

If so, see this...

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

[Richard Buttrey]

Hi,

Wouldn't it be simpler to avoid all the conversion stuff and merely enter
6/11/13

and have the cell formatted as "ddd mm/dd/yy"

[AlKey]

Try this formula if want to convert 61113 to real date

Please Login or Register  to view this content.

Apply desired format
or if you prefer as a text string

Please Login or Register  to view this content.

v	A	B
1	61113	Tue 06/11/13