formating date from general2 custom

  • Thread starter Thread starter vladimir.vaynshtok
  • Start date Start date
V

vladimir.vaynshtok

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.
Click to expand...

Any ideas?
Thanks for the help.

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

Any ideas?
Thanks for the help.

Vlad
Click to expand...

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.
 
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.
Click to expand...



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


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.
Click to expand...



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

| 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
|
|
| >
| > | >
| > >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
|
|
 
Back
Top