Custom Number format question

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 <[email protected]> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >