Prev: Simplify Weighted Average Formula
Next: maintaining constant percentage
From: Nitesh on 5 Jun 2010 00:34
Hi ,

I'm using Ms Excel 2003 and in my excel sheet there is a column in which
dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).

Can excel programming is required to convert such date or is there any excel
function.

Please suggest.

--
------------------------------
Thanks
Nitesh
------------------------------

From: FSt1 on 5 Jun 2010 01:27
hi
you could use text to columns
high light the date.
on the menu bar>data>text to columns
step 1 - select delimited, click next
step 2 - select tab, click next
step 3- upper right "column data format", select date.
from the drop down, select DMY, click finish.

regards
FSt1

"Nitesh" wrote:

> Hi ,
>
> I'm using Ms Excel 2003 and in my excel sheet there is a column in which
> dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
> wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).
>
> Can excel programming is required to convert such date or is there any excel
> function.
>
> Please suggest.
>
> --
> ------------------------------
> Thanks
> Nitesh
> ------------------------------
>
From: Nitesh on 5 Jun 2010 05:34
Hi Sir

Thanks a lot.
--
------------------------------
Thanks
Nitesh
------------------------------



"FSt1" wrote:

> hi
> you could use text to columns
> high light the date.
> on the menu bar>data>text to columns
> step 1 - select delimited, click next
> step 2 - select tab, click next
> step 3- upper right "column data format", select date.
> from the drop down, select DMY, click finish.
>
> regards
> FSt1
>
> "Nitesh" wrote:
>
> > Hi ,
> >
> > I'm using Ms Excel 2003 and in my excel sheet there is a column in which
> > dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
> > wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).
> >
> > Can excel programming is required to convert such date or is there any excel
> > function.
> >
> > Please suggest.
> >
> > --
> > ------------------------------
> > Thanks
> > Nitesh
> > ------------------------------
> >
From: Ron Rosenfeld on 5 Jun 2010 07:54
On Fri, 4 Jun 2010 21:34:29 -0700, Nitesh
<Nitesh(a)discussions.microsoft.com> wrote:

>Hi ,
>
>I�m using Ms Excel 2003 and in my excel sheet there is a column in which
>dates are entered as DD/MM/YYYY (e.g. 24/03/2010 as a text) format and I
>wanted to convert it into MM/DD/YYYY (e.g. 03/24/2010).
>
>Can excel programming is required to convert such date or is there any excel
>function.
>
>Please suggest.


=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

Format the result as a date, if it appears to be some five digit
number.

But be aware that if your entry people are also entering dates where
the day of the month is 12 or earlier AND the cell was not previously
formatted as TEXT, Excel will convert these into "real dates", and the
above method will not work.

If that is the case, you might consider:

=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,4,2),
LEFT(A1,2)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

 | 
Pages: 1
Prev: Simplify Weighted Average Formula
Next: maintaining constant percentage