Formatting Issue

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.

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

In article <wayliff.267eym_1144933803.0253@excelforum-nospam.com>,
wayliff <wayliff.267eym_1144933803.0253@excelforum-nospam.com> wrote:

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

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

The text formatting does not work.

Text Formatting BEFORE data entry works by me.

HTH
--
AP

"wayliff" <wayliff.267g4a_1144935301.5405@excelforum-nospam.com> a écrit
dans le message de
news:wayliff.267g4a_1144935301.5405@excelforum-nospam.com...
>
> I am not sure that the ( ' ) would viable unless vba can do it for
> me...not sure how.
>
> The text formatting does not work.
>
>
> --
> wayliff
> ------------------------------------------------------------------------
> wayliff's Profile:
http://www.excelforum.com/member.php...o&userid=29860
> View this thread: http://www.excelforum.com/showthread...hreadid=532598
>

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.

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.

wayliff wrote:
>
> 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
> ------------------------------------------------------------------------
> wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860
> View this thread: http://www.excelforum.com/showthread...hreadid=532598

--

Dave Peterson