Solved Formating to a date Excel 2010

Microsoft Office excel 2003
August 26, 2014 at 09:22:40
Specs: Windows 7
 Hi everyoneThis might be an easy one for whoever knows itI have a number 8202014I need to format it as a date to show 08/20/14And the formula should also work for 10202014 to show 10/20/14I cant make a formula with left, mid, right that works correctly.Thanks in advance!!!

See More: Formating to a date Excel 2010

August 26, 2014 at 13:26:14
 If you know that the cell will only contain 7 or 8 digits, you don't need the second IF clause. The logical_test will either be TRUE for 7 digits or FALSE for 8, so a single IF function is all that you need.This version is shorter and slightly more efficient:=IF(LEN(E10)=7,DATE(RIGHT(E10,4),LEFT(E10,1),MID(E10,2,2)),DATE(RIGHT(E10,4),LEFT(E10,2),MID(E10,3,2)))

#1
August 26, 2014 at 09:40:25
 Set the desired date format in Control Panel > Region & Language > FormatThat determines how the date will appear in all Microsoft applications.

Report •

#2
August 26, 2014 at 09:46:34
 Sorry, this is a corporate computer, access is extremly limited.I might be missing something but I dont see how that applied.Lets say I have "8202014" on cell A1I want it to show as 08/20/14 on cell A2And it shold also work for "10202014" to show 10/20/14I guess my problem is that it may be a 7 or 8 digit number the one that needs to be converted into mm/dd/yy.Thanks

Report •

#3
August 26, 2014 at 10:41:30
 OK... after some hard thinking I got it. Some other posts gave me the answer. Thanks!=IF(LEN(E10)=7,DATE(RIGHT(E10,4),LEFT(E10,1),MID(E10,2,2)), IF(LEN(E10)=8,DATE(RIGHT(E10,4),LEFT(E10,2),MID(E10,3,2))))

Report •

Related Solutions

#4
August 26, 2014 at 13:26:14