formating date from general2 custom

I have an input xls file with the date but like this:

Wed May 14 00:00:00 GMT-05:00 2008

I need to have just  following format

dd/mm/yyyy

I  tried to format it using  Format->Cells->Date or Format->Cells-
>Custom  but this is failing. Nothing changed.

Any ideas?
Thanks for the help.

Vlad
0
6/17/2008 5:20:01 PM
excel 39879 articles. 2 followers. Follow

6 Replies
579 Views

Similar Articles

[PageSpeed] 24

<vladimir.vaynshtok@gmail.com> wrote in message 
news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com...
>I have an input xls file with the date but like this:
>
> Wed May 14 00:00:00 GMT-05:00 2008
>
> I need to have just  following format
>
> dd/mm/yyyy
>
> I  tried to format it using  Format->Cells->Date or Format->Cells-
>>Custom  but this is failing. Nothing changed.
>
> Any ideas?
> Thanks for the help.
>
> Vlad

That should work. I suspect operator error there somewhere. However, another 
way to do it is as follows: In another part of the spreadsheet enter a date 
in the format you want. Then use the format painter.



0
NOTsomeone (57)
6/17/2008 5:33:32 PM
On Jun 17, 12:33=A0pm, "GB" <NOTsome...@microsoft.com> wrote:
> <vladimir.vaynsh...@gmail.com> wrote in message
>
> news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com...
>
> >I have an input xls file with the date but like this:
>
> > Wed May 14 00:00:00 GMT-05:00 2008
>
> > I need to have just =A0following format
>
> > dd/mm/yyyy
>
> > I =A0tried to format it using =A0Format->Cells->Date or Format->Cells-
> >>Custom =A0but this is failing. Nothing changed.
>
> > Any ideas?
> > Thanks for the help.
>
> > Vlad
>
> That should work. I suspect operator error there somewhere. However, anoth=
er
> way to do it is as follows: In another part of the spreadsheet enter a dat=
e
> in the format you want. Then use the format painter.



I tried. Copiig to new cell is not working.May be a problem with Excel
settings?
I checked conversion of numbers to other format ...   That works.

Wed May 14 00:00:00 GMT-05:00 2008  -> to anything like dd/mm/yyyyy

Thanks for any help,


Vlad
0
6/17/2008 6:30:09 PM
Hi Vlad,

Your dates are probably text. You can easily check with the ISTEXT() function.
You can convert to date with text functions.

=DATEVALUE(MID(A1,5,6)&", "&RIGHT(A1,4))

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel


<vladimir.vaynshtok@gmail.com> wrote in message news:7bdd73ac-b9a4-41f6-803e-aec3906c853e@f36g2000hsa.googlegroups.com...
On Jun 17, 12:33 pm, "GB" <NOTsome...@microsoft.com> wrote:
> <vladimir.vaynsh...@gmail.com> wrote in message
>
> news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com...
>
> >I have an input xls file with the date but like this:
>
> > Wed May 14 00:00:00 GMT-05:00 2008
>
> > I need to have just following format
>
> > dd/mm/yyyy
>
> > I tried to format it using Format->Cells->Date or Format->Cells-
> >>Custom but this is failing. Nothing changed.
>
> > Any ideas?
> > Thanks for the help.
>
> > Vlad
>
> That should work. I suspect operator error there somewhere. However, another
> way to do it is as follows: In another part of the spreadsheet enter a date
> in the format you want. Then use the format painter.



I tried. Copiig to new cell is not working.May be a problem with Excel
settings?
I checked conversion of numbers to other format ...   That works.

Wed May 14 00:00:00 GMT-05:00 2008  -> to anything like dd/mm/yyyyy

Thanks for any help,


Vlad 


0
nicolaus (2022)
6/17/2008 7:27:46 PM
Maybe you could use a helper cell and a formula like:
=--(MID(A1,9,2)&"-"&MID(A1,5,3)&"-"&RIGHT(A1,4))
(and give the cell a nice date format)

But that depends on the layout of the data.  3 character day, 3 character month,
2 character day, and last 4 characters as the year.

If the format varies, you may want to post more examples.

vladimir.vaynshtok@gmail.com wrote:
> 
> I have an input xls file with the date but like this:
> 
> Wed May 14 00:00:00 GMT-05:00 2008
> 
> I need to have just  following format
> 
> dd/mm/yyyy
> 
> I  tried to format it using  Format->Cells->Date or Format->Cells-
> >Custom  but this is failing. Nothing changed.
> 
> Any ideas?
> Thanks for the help.
> 
> Vlad

-- 

Dave Peterson
0
petersod (12005)
6/17/2008 7:29:13 PM
The date generated by the formula must be in one of the formats recognized; see International settings in Windows' Control Panel.

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:%23lsm1AL0IHA.1772@TK2MSFTNGP03.phx.gbl...
| Hi Vlad,
|
| Your dates are probably text. You can easily check with the ISTEXT() function.
| You can convert to date with text functions.
|
| =DATEVALUE(MID(A1,5,6)&", "&RIGHT(A1,4))
|
| -- 
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| <vladimir.vaynshtok@gmail.com> wrote in message news:7bdd73ac-b9a4-41f6-803e-aec3906c853e@f36g2000hsa.googlegroups.com...
| On Jun 17, 12:33 pm, "GB" <NOTsome...@microsoft.com> wrote:
| > <vladimir.vaynsh...@gmail.com> wrote in message
| >
| > news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com...
| >
| > >I have an input xls file with the date but like this:
| >
| > > Wed May 14 00:00:00 GMT-05:00 2008
| >
| > > I need to have just following format
| >
| > > dd/mm/yyyy
| >
| > > I tried to format it using Format->Cells->Date or Format->Cells-
| > >>Custom but this is failing. Nothing changed.
| >
| > > Any ideas?
| > > Thanks for the help.
| >
| > > Vlad
| >
| > That should work. I suspect operator error there somewhere. However, another
| > way to do it is as follows: In another part of the spreadsheet enter a date
| > in the format you want. Then use the format painter.
|
|
|
| I tried. Copiig to new cell is not working.May be a problem with Excel
| settings?
| I checked conversion of numbers to other format ...   That works.
|
| Wed May 14 00:00:00 GMT-05:00 2008  -> to anything like dd/mm/yyyyy
|
| Thanks for any help,
|
|
| Vlad
|
| 


0
nicolaus (2022)
6/17/2008 7:38:08 PM
Good point


"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:e6vToGL0IHA.3496@TK2MSFTNGP03.phx.gbl...
> The date generated by the formula must be in one of the formats 
> recognized; see International settings in Windows' Control Panel.
>
> -- 
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "Niek Otten" <nicolaus@xs4all.nl> wrote in message 
> news:%23lsm1AL0IHA.1772@TK2MSFTNGP03.phx.gbl...
> | Hi Vlad,
> |
> | Your dates are probably text. You can easily check with the ISTEXT() 
> function.
> | You can convert to date with text functions.
> |
> | =DATEVALUE(MID(A1,5,6)&", "&RIGHT(A1,4))
> |
> | -- 
> | Kind regards,
> |
> | Niek Otten
> | Microsoft MVP - Excel
> |
> |
> | <vladimir.vaynshtok@gmail.com> wrote in message 
> news:7bdd73ac-b9a4-41f6-803e-aec3906c853e@f36g2000hsa.googlegroups.com...
> | On Jun 17, 12:33 pm, "GB" <NOTsome...@microsoft.com> wrote:
> | > <vladimir.vaynsh...@gmail.com> wrote in message
> | >
> | > 
> news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com...
> | >
> | > >I have an input xls file with the date but like this:
> | >
> | > > Wed May 14 00:00:00 GMT-05:00 2008
> | >
> | > > I need to have just following format
> | >
> | > > dd/mm/yyyy
> | >
> | > > I tried to format it using Format->Cells->Date or Format->Cells-
> | > >>Custom but this is failing. Nothing changed.
> | >
> | > > Any ideas?
> | > > Thanks for the help.
> | >
> | > > Vlad
> | >
> | > That should work. I suspect operator error there somewhere. However, 
> another
> | > way to do it is as follows: In another part of the spreadsheet enter a 
> date
> | > in the format you want. Then use the format painter.
> |
> |
> |
> | I tried. Copiig to new cell is not working.May be a problem with Excel
> | settings?
> | I checked conversion of numbers to other format ...   That works.
> |
> | Wed May 14 00:00:00 GMT-05:00 2008  -> to anything like dd/mm/yyyyy
> |
> | Thanks for any help,
> |
> |
> | Vlad
> |
> |
>
> 


0
NOTsomeone (57)
6/17/2008 8:11:45 PM
Reply:

Similar Artilces: