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/14And 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!!!

✔ 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_testwill 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.

Set the desired date format in Control Panel > Region & Language > FormatThat determines how the date will appear in all Microsoft applications.

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

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))))

If you know that the cell will only contain 7 or 8 digits, you don't need the second IF clause. The logical_testwill 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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History