date format problem

[panji]

hi all,
I am creating a report using a tool called Nvison.
it uses a query tool that will return data and populate it to an excel
sheet. this query tool has the default date format yyyy/mm/dd which
cannot be changed.

In my excel file data output ,I want the date to be in the format
mm/dd/yyyy. Though i have defined custom date format(mm/dd/yyyy) for my
column the data is populated in the format yyyy/mm/dd in the excel
sheet.However when i double click on the cell and press enter the
format becomes mm/dd/yyyy which is the custom format that i have
defined(& the format that i want).

can anyone please suggest a method to solve this problem.
thanks in advance
jijo

 

[Dave WL]

Use the formulas =HOUR("CellREF"), =MINUTE("CellREF"), and
=SECOND("CellREF") to take the data out of the one cell and another,
but in the new order. Possible an idea - haven't actually tried it

 

[Dave WL]

Two columns - one with your entered data (A) and another in B.

In B1 put =A1 and fill down.

Format A as yyyy/mm/dd
Format B as mm/dd/yyyy

A nuisnace but occurs simply because excel isn't good at aut
updating.

Certainly worked when I tested it

HTH

Dav

 

[Ron Rosenfeld]

hi all,
I am creating a report using a tool called Nvison.
it uses a query tool that will return data and populate it to an excel
sheet. this query tool has the default date format yyyy/mm/dd which
cannot be changed.

In my excel file data output ,I want the date to be in the format
mm/dd/yyyy. Though i have defined custom date format(mm/dd/yyyy) for my
column the data is populated in the format yyyy/mm/dd in the excel
sheet.However when i double click on the cell and press enter the
format becomes mm/dd/yyyy which is the custom format that i have
defined(& the format that i want).

can anyone please suggest a method to solve this problem.
thanks in advance
jijo

Try this:

Select the column in which the dates appear.

Select Data/Text to Columns/Next/Next/
Column Data Format: Date/YMD
Finish

If it works, you could record a macro to do the same thing.


--ron