I want to display numbers with zeros in front of them, eg 005
 

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom




"Joel" wrote:

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

The # is invisible. I seem to be using the work visible/Invisible a lot
tonight.

"Tom" wrote:

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom




"Joel" wrote:

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

Hi again Joel,

Do you have to change a setting to make the # invisible?
I can see it in front of my numbers.

Cheers
Tom


"Joel" wrote:

The # is invisible. I seem to be using the work visible/Invisible a lot
tonight.

"Tom" wrote:

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom




"Joel" wrote:

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

One of my replies didn't get posted. Try this formula

=Text(0,"#000")

The formula should do the same thing as special format. You should see 3
zeroes with no #. I want to see if this works because it should using the
same internal function in excel as formating.

"Tom" wrote:


Hi again Joel,

Do you have to change a setting to make the # invisible?
I can see it in front of my numbers.

Cheers
Tom


"Joel" wrote:

The # is invisible. I seem to be using the work visible/Invisible a lot
tonight.

"Tom" wrote:

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom




"Joel" wrote:

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

If you want to see 5 digits, you can use a custom format of:
00000

If you want to see 3 digits, you can use a custom format of:
000


Tom wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

--

Dave Peterson

I want to display numbers with zeros in front of them, eg 005
 

Hi again Joel,

Thanks for the formula - it works for me, I have emailed it to my friend to
give it a go in her 'real' task - will let you know if it's successful.

Cheers
Tom

PS By any chance do you have a relatively simple formula for calculating
the days, months and years between two dates? The challenge is to administer
a school test to children on a certain date, then apply a formula which will
produce their exact age in years, months, at that point in time.
"Joel" wrote:

One of my replies didn't get posted. Try this formula

=Text(0,"#000")

The formula should do the same thing as special format. You should see 3
zeroes with no #. I want to see if this works because it should using the
same internal function in excel as formating.

"Tom" wrote:


Hi again Joel,

Do you have to change a setting to make the # invisible?
I can see it in front of my numbers.

Cheers
Tom


"Joel" wrote:

The # is invisible. I seem to be using the work visible/Invisible a lot
tonight.

"Tom" wrote:

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom




"Joel" wrote:

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

"Tom" wrote:

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

I want to display numbers with zeros in front of them, eg 005
 

Tom

Use the undocumented DATEDIF function.

See Chip Pearson's site for details.

http://www.cpearson.com/excel/datedif.aspx


Gord Dibben MS Excel MVP

On Sun, 8 Jun 2008 06:15:00 -0700, Tom wrote:

PS By any chance do you have a relatively simple formula for calculating
the days, months and years between two dates? The challenge is to administer
a school test to children on a certain date, then apply a formula which will
produce their exact age in years, months, at that point in time.