Changing a number into time

[Rick Rothstein \(MVP - VB\)]

Okay, I just got your spreadsheet.... you didn't tell us there was a minus
sign in front of the number 107... you can't have negative times. Use this
formula instead of the one I gave you...

=TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

If you want to show a minus sign in front of the formatted output, use this
formula instead...

=TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""")

Rick

Excell 2003

You can send it to me... just remove the NOSPAM stuff out of my email
address.

By the way, I don't remember if you posted this or not... what version of
Excel are you using?

Rick

I've got no where to post them to have a look but i' happily email them
to
some-one to have a gander! I can do basic formulaes but i'm stuggling
with
this one!

:

The cell that gives the 107 which is (k109) has the following formulae
in
it
=SUM(K99:K108) its a bisic number cell with no decimal places.

:

I think you are going to have to give us some additional
information.
If
there is a formula in the cell with the 107 in it, copy/paste it
into a
response to us. Also, tell us exactly what is displayed in the cell
(and
what side of the cell it is on... left or right side). If there is a
formula
in the cell, tell us what is in any referenced cells (that is, if
your
formula uses C5 in it, tell us what is in C5). Right-click the cell
and
select Format Cells from the popup menu... what item in the Category
list
(on the Number tab) is selected? If Custom, tell us what custom
format
string is shown in the Type field. Oh, and is this cell part of a
merged set
of cells? You could also tell us anything else you might have done
"out
of
the ordinary" to the cell.

Better yet would be if you could put the spreadsheet up on a webpage
somewhere so that we could download it and look at it directly (of
course,
if you do this, tell us what cell or cells we should be looking at).

Rick


Whatever i try doesn't give me what i want.
:

Wouldn't the A1/1440 part of my formula convert a "text number"
into
a
real
number in order to perform the division?

Rick


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
It sounds as if A1 doesn't contain a number, but instead
contains
some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but it
still
dunna
work!!!
Grrrrr pulling my hair out here!


:

At the end of the day it becomes a bit more complicated, it
could
be
107minutes. This is easy to understand in it's self. Yet
I
need a
formulae
that would convert the 107 (which is a general number with
no
decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

 

[James]

THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!!

It alwasy turns out to be something so small doesn't it!! THANK YOU

Okay, I just got your spreadsheet.... you didn't tell us there was a minus
sign in front of the number 107... you can't have negative times. Use this
formula instead of the one I gave you...

=TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

If you want to show a minus sign in front of the formatted output, use this
formula instead...

=TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""")

Rick

Excell 2003

You can send it to me... just remove the NOSPAM stuff out of my email
address.

By the way, I don't remember if you posted this or not... what version of
Excel are you using?

Rick


I've got no where to post them to have a look but i' happily email them
to
some-one to have a gander! I can do basic formulaes but i'm stuggling
with
this one!

:

The cell that gives the 107 which is (k109) has the following formulae
in
it
=SUM(K99:K108) its a bisic number cell with no decimal places.

:

I think you are going to have to give us some additional
information.
If
there is a formula in the cell with the 107 in it, copy/paste it
into a
response to us. Also, tell us exactly what is displayed in the cell
(and
what side of the cell it is on... left or right side). If there is a
formula
in the cell, tell us what is in any referenced cells (that is, if
your
formula uses C5 in it, tell us what is in C5). Right-click the cell
and
select Format Cells from the popup menu... what item in the Category
list
(on the Number tab) is selected? If Custom, tell us what custom
format
string is shown in the Type field. Oh, and is this cell part of a
merged set
of cells? You could also tell us anything else you might have done
"out
of
the ordinary" to the cell.

Better yet would be if you could put the spreadsheet up on a webpage
somewhere so that we could download it and look at it directly (of
course,
if you do this, tell us what cell or cells we should be looking at).

Rick


Whatever i try doesn't give me what i want.
:

Wouldn't the A1/1440 part of my formula convert a "text number"
into
a
real
number in order to perform the division?

Rick


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
It sounds as if A1 doesn't contain a number, but instead
contains
some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but it
still
dunna
work!!!
Grrrrr pulling my hair out here!


:

At the end of the day it becomes a bit more complicated, it
could
be
107minutes. This is easy to understand in it's self. Yet
I
need a
formulae
that would convert the 107 (which is a general number with
no
decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, better still, use this formula and it will automatically adjust
for negative values and display the minus sign when necessary...

=IF(K109<0,"-","")&TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

Rick

Okay, I just got your spreadsheet.... you didn't tell us there was a minus
sign in front of the number 107... you can't have negative times. Use this
formula instead of the one I gave you...

=TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

If you want to show a minus sign in front of the formatted output, use
this formula instead...

=TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""")

Rick

Excell 2003

You can send it to me... just remove the NOSPAM stuff out of my email
address.

By the way, I don't remember if you posted this or not... what version
of
Excel are you using?

Rick


I've got no where to post them to have a look but i' happily email
them to
some-one to have a gander! I can do basic formulaes but i'm stuggling
with
this one!

:

The cell that gives the 107 which is (k109) has the following
formulae in
it
=SUM(K99:K108) its a bisic number cell with no decimal places.

:

I think you are going to have to give us some additional
information.
If
there is a formula in the cell with the 107 in it, copy/paste it
into a
response to us. Also, tell us exactly what is displayed in the cell
(and
what side of the cell it is on... left or right side). If there is
a
formula
in the cell, tell us what is in any referenced cells (that is, if
your
formula uses C5 in it, tell us what is in C5). Right-click the cell
and
select Format Cells from the popup menu... what item in the
Category
list
(on the Number tab) is selected? If Custom, tell us what custom
format
string is shown in the Type field. Oh, and is this cell part of a
merged set
of cells? You could also tell us anything else you might have done
"out
of
the ordinary" to the cell.

Better yet would be if you could put the spreadsheet up on a
webpage
somewhere so that we could download it and look at it directly (of
course,
if you do this, tell us what cell or cells we should be looking
at).

Rick


Whatever i try doesn't give me what i want.
:

Wouldn't the A1/1440 part of my formula convert a "text number"
into
a
real
number in order to perform the division?

Rick


"David Biddulph" <groups [at] biddulph.org.uk> wrote in message
It sounds as if A1 doesn't contain a number, but instead
contains
some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but
it
still
dunna
work!!!
Grrrrr pulling my hair out here!


:

At the end of the day it becomes a bit more complicated,
it
could
be
107minutes. This is easy to understand in it's self. Yet
I
need a
formulae
that would convert the 107 (which is a general number with
no
decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

 

[Rick Rothstein \(MVP - VB\)]

You are quite welcome. For future questions you might ask in these
newsgroups... if we ask you what value is display in a cell, and the value
is negative, tell us it is negative... don't refer to it as a positive
number even if that is how you "think" of it (for example, as a deficit
interval).

Rick

THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!!

It alwasy turns out to be something so small doesn't it!! THANK YOU

Okay, I just got your spreadsheet.... you didn't tell us there was a
minus
sign in front of the number 107... you can't have negative times. Use
this
formula instead of the one I gave you...

=TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

If you want to show a minus sign in front of the formatted output, use
this
formula instead...

=TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""")

Rick

Excell 2003


:

You can send it to me... just remove the NOSPAM stuff out of my email
address.

By the way, I don't remember if you posted this or not... what version
of
Excel are you using?

Rick


I've got no where to post them to have a look but i' happily email
them
to
some-one to have a gander! I can do basic formulaes but i'm
stuggling
with
this one!

:

The cell that gives the 107 which is (k109) has the following
formulae
in
it
=SUM(K99:K108) its a bisic number cell with no decimal places.

:

I think you are going to have to give us some additional
information.
If
there is a formula in the cell with the 107 in it, copy/paste it
into a
response to us. Also, tell us exactly what is displayed in the
cell
(and
what side of the cell it is on... left or right side). If there
is a
formula
in the cell, tell us what is in any referenced cells (that is, if
your
formula uses C5 in it, tell us what is in C5). Right-click the
cell
and
select Format Cells from the popup menu... what item in the
Category
list
(on the Number tab) is selected? If Custom, tell us what custom
format
string is shown in the Type field. Oh, and is this cell part of a
merged set
of cells? You could also tell us anything else you might have
done
"out
of
the ordinary" to the cell.

Better yet would be if you could put the spreadsheet up on a
webpage
somewhere so that we could download it and look at it directly
(of
course,
if you do this, tell us what cell or cells we should be looking
at).

Rick


Whatever i try doesn't give me what i want.
:

Wouldn't the A1/1440 part of my formula convert a "text
number"
into
a
real
number in order to perform the division?

Rick


"David Biddulph" <groups [at] biddulph.org.uk> wrote in
message
It sounds as if A1 doesn't contain a number, but instead
contains
some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but
it
still
dunna
work!!!
Grrrrr pulling my hair out here!


:

At the end of the day it becomes a bit more complicated,
it
could
be
107minutes. This is easy to understand in it's self.
Yet
I
need a
formulae
that would convert the 107 (which is a general number
with
no
decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick

 

[James]

I never even thought of that!
Small things bring us down eh.

You are quite welcome. For future questions you might ask in these
newsgroups... if we ask you what value is display in a cell, and the value
is negative, tell us it is negative... don't refer to it as a positive
number even if that is how you "think" of it (for example, as a deficit
interval).

Rick

THANK YOU VERY MUCH THAT DOES THE JOB EXACTLLY AS I WANTED!!!

It alwasy turns out to be something so small doesn't it!! THANK YOU

Okay, I just got your spreadsheet.... you didn't tell us there was a
minus
sign in front of the number 107... you can't have negative times. Use
this
formula instead of the one I gave you...

=TEXT(ABS(K109)/1440,"[h] ""hours"" mm ""minutes""")

If you want to show a minus sign in front of the formatted output, use
this
formula instead...

=TEXT(ABS(K109)/1440,"-[h] ""hours"" mm ""minutes""")

Rick


Excell 2003


:

You can send it to me... just remove the NOSPAM stuff out of my email
address.

By the way, I don't remember if you posted this or not... what version
of
Excel are you using?

Rick


I've got no where to post them to have a look but i' happily email
them
to
some-one to have a gander! I can do basic formulaes but i'm
stuggling
with
this one!

:

The cell that gives the 107 which is (k109) has the following
formulae
in
it
=SUM(K99:K108) its a bisic number cell with no decimal places.

:

I think you are going to have to give us some additional
information.
If
there is a formula in the cell with the 107 in it, copy/paste it
into a
response to us. Also, tell us exactly what is displayed in the
cell
(and
what side of the cell it is on... left or right side). If there
is a
formula
in the cell, tell us what is in any referenced cells (that is, if
your
formula uses C5 in it, tell us what is in C5). Right-click the
cell
and
select Format Cells from the popup menu... what item in the
Category
list
(on the Number tab) is selected? If Custom, tell us what custom
format
string is shown in the Type field. Oh, and is this cell part of a
merged set
of cells? You could also tell us anything else you might have
done
"out
of
the ordinary" to the cell.

Better yet would be if you could put the spreadsheet up on a
webpage
somewhere so that we could download it and look at it directly
(of
course,
if you do this, tell us what cell or cells we should be looking
at).

Rick


Whatever i try doesn't give me what i want.
:

Wouldn't the A1/1440 part of my formula convert a "text
number"
into
a
real
number in order to perform the division?

Rick


"David Biddulph" <groups [at] biddulph.org.uk> wrote in
message
It sounds as if A1 doesn't contain a number, but instead
contains
some
form of text. What does =ISNUMBER(A1) show?
--
David Biddulph

Thank but that just gives me a reply of #VALUE
Am i doing something wrong in my formulae?
I copied and paste yours and changed the cell reference but
it
still
dunna
work!!!
Grrrrr pulling my hair out here!


:

At the end of the day it becomes a bit more complicated,
it
could
be
107minutes. This is easy to understand in it's self.
Yet
I
need a
formulae
that would convert the 107 (which is a general number
with
no
decimal
places)
into a time which should be 1 Hour 47 minutes

This formula will do that...

=TEXT(A1/1440,"[h] ""hours"" mm ""minutes""")

where A1 is assumed to have your 107 minute result.

Rick