Prev: Excel 2010
Next: IF need to add a third value
From: מיכאל (מיקי) אבידן on 5 Apr 2010 07:10
This is a "different" symphony and answers the OP question.
Micky


"Niek Otten" wrote:

> =TEXT(--TEXT(A1,"0000\-00\-00"),"mm/dd/yyyy")
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "מיכאל (מיקי) אבידן" <micky-a*at*tapuz.co.il> wrote in message
> news:5C476AAE-3E46-4EE3-BF08-D3B543A7E9FC(a)microsoft.com...
> > If I'm not mistaken - you have missed the important part [quote]:
> > ...I need to convert this text to another date format showing mm/dd/yyyy
> > !!!
> > IN TEXT !!! for importing into another system. [end quote]
> > Micky
> >
> >
> > "David Biddulph" wrote:
> >
> >> =--TEXT(A1,"0000\-00\-00") and format as date to suit.
> >> --
> >> David Biddulph
> >>
> >>
> >> "Terry0928" <u59143(a)uwe> wrote in message news:a610c18eb8b14(a)uwe...
> >> > Hi,
> >> >
> >> > I have some data exported from a system, the date is a text displayed
> >> > as
> >> > "20100315" which should read yyyymmdd. I need to convert this text to
> >> > another date format showing mm/dd/yyyy in text for importing into
> >> > another
> >> > system. As the text format yyyymmdd does not contain any "/" or "-"
> >> > so
> >> > using text to column will be a difficulty.
> >> >
> >> > Please advise how could I handle this.
> >> >
> >> > Thanks a lot.
> >> >
> >> > Best Regards,
> >> > Terry
> >> >
> >>
> >> .
> >>
>
From: Ms-Exl-Learner on 5 Apr 2010 21:11
If your System Date setting in Control Panel is MM-DD-YYYY then the below
formula will work fine.
=IF(A1="","",VALUE(MID(TRIM(A1),5,2)&"/"&RIGHT(TRIM(A1),2)&"/"&LEFT(TRIM(A1),4)))

If your System Date setting in Control Panel is DD-MM-YYYY then the below
formula will work fine.
=IF(A1="","",VALUE(RIGHT(TRIM(A1),2)&"/"&MID(TRIM(A1),5,2)&"/"&LEFT(TRIM(A1),4)))

To check your system Date Format:-
Run>> Type control.exe>>Regional and Language
Options>>Customize>>Date>>Short Date format>>Check whether it is DD-MM-YYYY
or MM-DD-YYYY
In Long Date Format Also. Use the formula based on your Control Panel Date
Setting.

--------------------
(Ms-Exl-Learner)
--------------------


"Terry0928" wrote:

> Ms-Exl-Learner wrote:
> >Assume that you are having the data like the below:
> >
> >A1 cell
> >20100315
> >
> >Copy and paste the below formula in B1 Cell
> >
> >=IF(A1="","",MID(TRIM(A1),5,2)&"/"&RIGHT(TRIM(A1),2)&"/"&LEFT(TRIM(A1),4))
> >
> >Copy the B1 cell and paste it to the remaining cells of B Column based on
> >the A Column Data.
> >But the above formula will get you the Text Date instead of Real Date.
> >
> >For getting the Real Date Use the below formula:-
> >
> >=IF(A1="","",VALUE(RIGHT(TRIM(A1),2)&"/"&MID(TRIM(A1),5,2)&"/"&LEFT(TRIM(A1),4)))
> >
> >Choose the desired date format from Format Cells.
> >
> >Change the cell reference A1 in the above formula to your desired cell.
> >
> >> Hi,
> >>
> >[quoted text clipped - 12 lines]
> >>
> >> Hi Ms-Exl-Learner,
>
> Thanks for your prompt response.
>
> I tried to work the two formula on my data on cell with text "20100201"
> I tried fomula =IF(AI2="","",MID(TRIM(AI2),5,2)&"/"&RIGHT(TRIM(AI2),2)
> &"/"&LEFT(TRIM(AI2),4)), it returned 02/01/2010. It works
>
> but when I tried to get the value with the fomula =IF(AI2="","",VALUE(RIGHT
> (TRIM(AI2),2)&"/"&MID(TRIM(AI2),5,2)&"/"&LEFT(TRIM(AI2),4))), it returned
> #VALUE!
>
> I tried to change the fomate of the cell to a date formate but nothing
> changed.
>
> don't understand what is wrong.
>
> Please advise.
>
> Thanks Terry
>
> .
>
First  |  Prev  | 
Pages: 1 2 3
Prev: Excel 2010
Next: IF need to add a third value