I have a sheet with date entries which are not in particular format.
Year is in column B.
Date and month are in column B with different formats.
In some rows in date column the full date is in dd-mm-yyyy format.
I want to get a result as dd-mmm-yyyy.
Since there are different formats, if I have to use a formula for each format, please suggest me. I will convert set by set using the guidelines given by any expert.
<tbody>
</tbody>I tried several methods. But of no use.
In G2 i used =mid(c2,1,2) and got 04.
In H2 i used =mid(c2,3,2) and got 18.
In I2 i used =date(b2,g,h2) and got 18-Apr-2016. This cell is formatted as date with 14-mar-2001 US format .
But in slno 490, the third in this example, instead of 14-Sep-2016, the result is 9-Feb-2017.
And in slno 600, the fourth in this example, instead of 18-Oct-2016, the result is 10-Jun-2017.
Please help with right solution.
Thanking you.
Year is in column B.
Date and month are in column B with different formats.
In some rows in date column the full date is in dd-mm-yyyy format.
I want to get a result as dd-mmm-yyyy.
Since there are different formats, if I have to use a formula for each format, please suggest me. I will convert set by set using the guidelines given by any expert.
S. No. | Year | Date | Expected Result |
1 | 2016 | 0418 | 18-Apr-2016 |
489 | 2016 | 1109 | 11-Sep-2016 |
490 | 2016 | 1409 | 14-Sep-2016 |
600 | 2016 | 1810 | 18-Oct-2016 |
601 | 18-10-2016 | 18-Oct-2016 | |
631 | 2016 | 10/26 | 26-Oct-2016 |
653 | 2016 | 2-Nov | 2-Nov-2016 |
654 | 2016 | 11/02 | 2-Nov-2016 |
1012 | 2017 | 21-Mar | 21-Mar-2017 |
1018 | 2017 | 22/3 | 22-Mar2017 |
1789 | 2017 | 6-Dec | 6-Dec-2017 |
<tbody>
</tbody>
In G2 i used =mid(c2,1,2) and got 04.
In H2 i used =mid(c2,3,2) and got 18.
In I2 i used =date(b2,g,h2) and got 18-Apr-2016. This cell is formatted as date with 14-mar-2001 US format .
But in slno 490, the third in this example, instead of 14-Sep-2016, the result is 9-Feb-2017.
And in slno 600, the fourth in this example, instead of 18-Oct-2016, the result is 10-Jun-2017.
Please help with right solution.
Thanking you.