Hyphen issue with Excel

elSicomoro · 09-25-2007, 10:09 AM

Hey folks,

I'm trying to type up a schedule for the drivers at my job; however, I keep running into the following problem: I want to type "11-4," for a shift, but Excel keeps turning that into a date. How can I type something like that into a cell without it turning into a date? I've tried using the number settings, but it keeps turning the number into something like 39,000.

Thanks.

Flint · 09-25-2007, 10:14 AM

right-click, format cells, number tab, format as text

select a block of cells (shift-click) and format them before you enter the times

Shawnee123 · 09-25-2007, 10:16 AM

Try:

Right click the entire column you're using. Format cells. On the number tab choose "text."

elSicomoro · 09-25-2007, 10:17 AM

Goddamnit...why the fuck couldn't I think of that?!

Thanks, Flint!

Shawnee123 · 09-25-2007, 10:17 AM

Or, what Flint said.

Flint · 09-25-2007, 10:20 AM

I'm just curious how 4-Nov translates to 39,390 ... ???

Shawnee123 · 09-25-2007, 10:22 AM

I KNOW...I was getting that too.

I was just doing spreadsheet work this morning...combining two involved sheets into one, and I still get lost on some of the intricacies (doing v-lookups.)

BigV · 09-25-2007, 10:27 AM

gregorian vs julian

Shawnee123 · 09-25-2007, 10:30 AM

Oh.

Who would win in a fight: Gregorian, Julian, Conan, or Chuck Norris?

Pie · 09-25-2007, 10:35 AM

You can also type the line as

'11-4

The initial apostrophe keeps it formatted as text, but isn't displayed.

Shawnee123 · 09-25-2007, 10:49 AM

That's a good tip. It will mess up formulas (because though not displayed it's still there,) but if you're not using it for anything like that it will work just fine.

Today I learned something at the Cellar that I can apply to work. How's that for serendipity?

limey · 09-25-2007, 01:11 PM

A little late but what Pie said ...
I lurve Excel

HungLikeJesus · 09-28-2007, 05:32 PM

I just saw this interesting article. I tried it and Excel did get the wrong answer (actually, I think it's just displaying it in the wrong format). I'm using Excel 2007.

09-25-2007, 10:09 AM	#1
elSicomoro Person who doesn't update the user title Join Date: Jan 2001 Posts: 12,486	Hyphen issue with Excel Hey folks, I'm trying to type up a schedule for the drivers at my job; however, I keep running into the following problem: I want to type "11-4," for a shift, but Excel keeps turning that into a date. How can I type something like that into a cell without it turning into a date? I've tried using the number settings, but it keeps turning the number into something like 39,000. Thanks.

09-25-2007, 10:14 AM	#2
Flint Snowflake Join Date: Mar 2006 Location: Dystopia Posts: 13,136	right-click, format cells, number tab, format as text select a block of cells (shift-click) and format them before you enter the times __________________ **************** There's a level of facility that everyone needs to accomplish, and from there it's a matter of deciding for yourself how important ultra-facility is to your expression. ... I found, like Joseph Campbell said, if you just follow whatever gives you a little joy or excitement or awe, then you're on the right track. . . . . . . . . . . . . . . . . . . . . . . . . . . Terry Bozzio**

09-25-2007, 10:16 AM	#3
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	Try: Right click the entire column you're using. Format cells. On the number tab choose "text." __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2007, 10:17 AM	#5
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	Or, what Flint said. __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2007, 10:20 AM	#6
Flint Snowflake Join Date: Mar 2006 Location: Dystopia Posts: 13,136	I'm just curious how 4-Nov translates to 39,390 ... ??? __________________ **************** There's a level of facility that everyone needs to accomplish, and from there it's a matter of deciding for yourself how important ultra-facility is to your expression. ... I found, like Joseph Campbell said, if you just follow whatever gives you a little joy or excitement or awe, then you're on the right track. . . . . . . . . . . . . . . . . . . . . . . . . . . Terry Bozzio**

09-25-2007, 10:22 AM	#7
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	I KNOW...I was getting that too. I was just doing spreadsheet work this morning...combining two involved sheets into one, and I still get lost on some of the intricacies (doing v-lookups.) __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2007, 10:27 AM	#8
BigV Goon Squad Leader Join Date: Nov 2004 Location: Seattle Posts: 27,063	gregorian vs julian __________________ Be Just and Fear Not.

09-25-2007, 10:30 AM	#9
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	Oh. Who would win in a fight: Gregorian, Julian, Conan, or Chuck Norris? __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2007, 10:35 AM	#10
Pie Gone and done Join Date: Sep 2001 Posts: 4,808	You can also type the line as '11-4 The initial apostrophe keeps it formatted as text, but isn't displayed. __________________ per·son \ˈpər-sən\ (noun) - an ephemeral collection of small, irrational decisions The fun thing about evolution (and science in general) is that it happens whether you believe in it or not.

09-25-2007, 10:49 AM	#11
Shawnee123 Why, you're a regular Alfred E Einstein, ain't ya? Join Date: Jun 2006 Posts: 21,206	That's a good tip. It will mess up formulas (because though not displayed it's still there,) but if you're not using it for anything like that it will work just fine. Today I learned something at the Cellar that I can apply to work. How's that for serendipity? __________________ A word to the wise ain't necessary - it's the stupid ones who need the advice. --Bill Cosby

09-25-2007, 01:11 PM	#12
limey Encroaching on your decrees Join Date: Feb 2004 Location: An island within the south-west coast of Scotland Posts: 7,016	A little late but what Pie said ... I lurve Excel __________________ Living it up on the edge ... of civilisation, within the southwest coast of

09-28-2007, 05:32 PM	#13
HungLikeJesus Only looks like a disaster tourist Join Date: Feb 2007 Location: above 7,000 feet Posts: 7,208	Excel Thinks 65,535 = 100,000 I just saw this interesting article. I tried it and Excel did get the wrong answer (actually, I think it's just displaying it in the wrong format). I'm using Excel 2007. __________________ Keep Your Bodies Off My Lawn SteveDallas's Random Thread Picker.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)