Home/ Questions/Q 513
In Process
computing
  • 26
Asked: 2021-09-03T20:01:43+00:00

Solved Formating to a date Excel 2010

  • 26

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

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.

Share

    1 Answer

    1. 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.

      • 0