Solved Formating to a date Excel 2010

Microsoft Office excel 2003
August 26, 2014 at 09:22:40
Specs: Windows 7
Hi everyone

This might be an easy one for whoever knows it

I have a number 8202014
I need to format it as a date to show 08/20/14

And the formula should also work for 10202014 to show 10/20/14

I cant make a formula with left, mid, right that works correctly.

Thanks in advance!!!


See More: Formating to a date Excel 2010

Report •

✔ Best Answer
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)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 26, 2014 at 09:40:25
Set the desired date format in Control Panel > Region & Language > Format

That 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 A1
I want it to show as 08/20/14 on cell A2
And it shold also work for "10202014" to show 10/20/14
I 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
✔ Best Answer
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)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question