Formatting Issue

[wayliff]

I have a problem with julian dates together with identification # that
come into a grid through a web query.

Example:
The julian date plus the id number is 101:01

Excel immediately assumes this is time and changes it to 101:01:00 and
so the formulas that convert my julian dates to regular dates dont work
for these fields.
And the formula bar would look something like 1/4/1900 5:01:00 AM.
Which is not my desired value.
I just want 101:01.

Of course julian dates under a hundred such as 098:01 work fine.

I have not been able to format the cells or find a way to have excel
not do this or create a custom format.

Any suggestions/help appreciated.

 

[JE McGimpsey]

Format the cells as Text, or prefix your entry with an apostrophe (')

 

[wayliff]

I am not sure that the ( ' ) would viable unless vba can do it for
me...not sure how.

The text formatting does not work.

 

[Ardus Petus]

Text Formatting BEFORE data entry works by me.

HTH
--
AP

"wayliff" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[wayliff]

this does not work with the routine I'm using.
I should have explained a bit better.

The end result is in a shared workbook and as you may know excel does
not allow the use of macros on shared workbooks.

So even if I format the target spreadsheet with text, when I do the
actual copy and paste excel changes the format to custom
[h]:mm:ss...and then my problem happens again.

 

[Dave Peterson]

First, you can use macros in a shared workbook--you just can't edit/view them.

And if you format the cell as text, then copy from the formula bar and paste
into the formula bar, I bet it works.

this does not work with the routine I'm using.
I should have explained a bit better.

The end result is in a shared workbook and as you may know excel does
not allow the use of macros on shared workbooks.

So even if I format the target spreadsheet with text, when I do the
actual copy and paste excel changes the format to custom
[h]:mm:ss...and then my problem happens again.

 

[wayliff]

the query is first pasted on a txt file and then opened with excel...the
formatting that excel wants is already in place by then. If I format the
cell as text...before or after...I get the same problem...from the
formula bar the value is a date with time.

maybe i'm just too stupid...

 

[wayliff]

ok i figured it out...format as text while i open the text
file...thanks to everybody.