Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh

Joh,

I am assuming that you only need formats for 0-0.9, and = 1.So use

[<0.99]0.0;0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joh" wrote in message
...
Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and
add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a
number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh

Thanks a lot Bob. It has almost solved my problem!
What I forgot to write in my question is that I use the Accounting number
format with Symbol set to none. This is to get a line instead of 0 when
there's nothing in the cells.
This is the number format I use at the moment, _-* #,##0_-;-* #,##0_-;_-*
"-"_-;_-@_-

Could you maybe help me to modify this with the format you just gave me?

Thanks so much for the help!


"Bob Phillips" wrote:

Joh,

I am assuming that you only need formats for 0-0.9, and = 1.So use

[<0.99]0.0;0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joh" wrote in message
...
Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and
add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a
number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh

Check out Microsoft website for explanation of custom formats:
http://office.microsoft.com/en-us/as...986791033.aspx

Gary

"Joh" wrote:

Thanks a lot Bob. It has almost solved my problem!
What I forgot to write in my question is that I use the Accounting number
format with Symbol set to none. This is to get a line instead of 0 when
there's nothing in the cells.
This is the number format I use at the moment, _-* #,##0_-;-* #,##0_-;_-*
"-"_-;_-@_-

Could you maybe help me to modify this with the format you just gave me?

Thanks so much for the help!


"Bob Phillips" wrote:

Joh,

I am assuming that you only need formats for 0-0.9, and = 1.So use

[<0.99]0.0;0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joh" wrote in message
...
Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and
add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a
number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh

On Tue, 25 Oct 2005 03:46:03 -0700, Joh wrote:

Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh

Not sure of your question.

But if you leave the cell formatted as General, then '1' will be displayed as
'1' and '.5' as '0.5'.


--ron

Thanks a lot you two! Finally solved my problem!

By using [=0.5]0.0;[<0.1]-;0 I can display 0.5 with one decimal. Everything
below 0.1 as - and everything else with no decimals.

Thanks again. Top advice!

"Gary Rowe" wrote:

Check out Microsoft website for explanation of custom formats:
http://office.microsoft.com/en-us/as...986791033.aspx

Gary

"Joh" wrote:

Thanks a lot Bob. It has almost solved my problem!
What I forgot to write in my question is that I use the Accounting number
format with Symbol set to none. This is to get a line instead of 0 when
there's nothing in the cells.
This is the number format I use at the moment, _-* #,##0_-;-* #,##0_-;_-*
"-"_-;_-@_-

Could you maybe help me to modify this with the format you just gave me?

Thanks so much for the help!


"Bob Phillips" wrote:

Joh,

I am assuming that you only need formats for 0-0.9, and = 1.So use

[<0.99]0.0;0

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joh" wrote in message
...
Hi all,

I have been thinking about this for a while now without coming up with a
sollution. Hopefully someone here will be able to help me.

I have a big holiday and absence spreadsheet that I put in all my
departments leave and sickness into. I put 1 in a cell for a whole day and
0.5 for half.

The problem I have is, to be able to display the 0.5 I have to go in and
add
a decimal place for that cell manually. If I add a decimal place for all
cells 1 becomes 1.0 which I don't want.

My question is, is there a way to display a decimal place only when a
number
has a positive number after the decimal sign?
So 0.5 should be displayed as 0.5 and not 1 and 1 should be displayed as 1
and not 1.0.

I've been trying to make a custom number format for this but maybe that's
not an option?

Hope the question is not too confusing.

Looking forward to your comments!

Thanks
Joh