Hi.

I am working on a worksheet witch draws data from another application. All
drawn data appears fine on the worksheet but the date columns. They are left
aligned (instead of the deafult right) and if I try to format them in a
different way by selecting them right-clicking and choosing Format Cells but
nothing happens. Even if I try to format them in any other option given
nothing happens. The cells remain in the dd/mm/yyyy format that they are (and
right aligned of course). What can I do? I am at a dead end here.

I need them to show up as dates so I can use a sumproduct function on them
to look for dates.

Thank you.

perhaps the dates are in text format in the webpage. it happened to me once.
I solved it perhaps in a tortuous way;;.

suppose the dateis in E5. tthe entry is 12/11/2004
which 12 november 2004

first you must change the format from text to date
in an empty cell enter 1(number 1)
highlight this <1) and go to E5 edit=pastespecial-multiply
you will get E5 and number denoting a date. but if you format this number as
date you will get 11 dec 2004 this is not what you want

in some other cell type this
=MONTH(E5)&"/"&DAY(E5)&"/"&YEAR(E5)

you will get 11/12/2004

and if you format this as date
you will get 12 november 2004 and can be manipulated as date.

try this till some MVP gives an elegant solutionl


bay wrote in message
...
Hi.

I am working on a worksheet witch draws data from another application. All
drawn data appears fine on the worksheet but the date columns. They are
left
aligned (instead of the deafult right) and if I try to format them in a
different way by selecting them right-clicking and choosing Format Cells
but
nothing happens. Even if I try to format them in any other option given
nothing happens. The cells remain in the dd/mm/yyyy format that they are
(and
right aligned of course). What can I do? I am at a dead end here.

I need them to show up as dates so I can use a sumproduct function on them
to look for dates.

Thank you.

some correction to my message


R.VENKATARAMAN $$$ wrote in message
...
perhaps the dates are in text format in the webpage. it happened to me
once.
I solved it perhaps in a tortuous way;;.

suppose the dateis in E5. tthe entry is 12/11/2004
which 12 november 2004

first you must change the format from text to date
in an empty cell enter 1(number 1)
highlight this <1 edit copy and go to E5 edit=pastespecial-multiply
you will get E5 and number denoting a date. but if you format this number
as
date you will get 11 dec 2004 this is not what you want

in some other cell type this
=MONTH(E5)&"/"&DAY(E5)&"/"&YEAR(E5)

you will get 11/12/2004

and if you format this as date
you will get 12 november 2004 and can be manipulated as date.

try this till some MVP gives an elegant solutionl


bay wrote in message
...
Hi.

I am working on a worksheet witch draws data from another application.
All
drawn data appears fine on the worksheet but the date columns. They are
left
aligned (instead of the deafult right) and if I try to format them in a
different way by selecting them right-clicking and choosing Format Cells
but
nothing happens. Even if I try to format them in any other option given
nothing happens. The cells remain in the dd/mm/yyyy format that they are
(and
right aligned of course). What can I do? I am at a dead end here.

I need them to show up as dates so I can use a sumproduct function on
them
to look for dates.

Thank you.

bay

Try DataText to ColumnsNextNextColumn Data FormatDate and pick the
appropriate format then Finish.


Gord Dibben Excel MVP


On Wed, 26 Jan 2005 02:03:01 -0800, bay wrote:

Hi.

I am working on a worksheet witch draws data from another application. All
drawn data appears fine on the worksheet but the date columns. They are left
aligned (instead of the deafult right) and if I try to format them in a
different way by selecting them right-clicking and choosing Format Cells but
nothing happens. Even if I try to format them in any other option given
nothing happens. The cells remain in the dd/mm/yyyy format that they are (and
right aligned of course). What can I do? I am at a dead end here.

I need them to show up as dates so I can use a sumproduct function on them
to look for dates.

Thank you.