Have 2 cells formated as time (13.30). I'd like to subtract the start time
16.00 from 24.00 to get a numerical number 8 (format here is number 2 dec
places). Problem is that I am having trouble formating the 24.00 entry to
read 24 hundred hours (24:00), excel puts the entry(24:00) as 00.00 so the
formula reads it as zero hour, hence the equation calculates as 00:00 -
16:00 = -16.00.

The sheet is used for scheduling and is printed and posted. Getting excel
to display (and calculate 24:00) would be a big help.

Thanks in advance

Use a number format of [hh]:mm or [hh]:mm:ss. The square brackets
prevent Excel from "rolling over" the time at 24 hours. Thus, for
example, 30 hours 40 minutes will be displayed as 30:40 or 30:40:00
rather than 6:00.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Sat, 7 Feb 2009 10:41:01 -0800, DaveB
wrote:

Have 2 cells formated as time (13.30). I'd like to subtract the start time
16.00 from 24.00 to get a numerical number 8 (format here is number 2 dec
places). Problem is that I am having trouble formating the 24.00 entry to
read 24 hundred hours (24:00), excel puts the entry(24:00) as 00.00 so the
formula reads it as zero hour, hence the equation calculates as 00:00 -
16:00 = -16.00.

The sheet is used for scheduling and is printed and posted. Getting excel
to display (and calculate 24:00) would be a big help.

Thanks in advance

Hi
First format cell Custom [hh]:mm.
Make sure you enter your time with colons i.e. 24:00
e.g. A1= 24:00 B1= 16:00 C1= A1-B1 result 08:00
HTH
John
"DaveB" wrote in message
...
Have 2 cells formated as time (13.30). I'd like to subtract the start
time
16.00 from 24.00 to get a numerical number 8 (format here is number 2 dec
places). Problem is that I am having trouble formating the 24.00 entry to
read 24 hundred hours (24:00), excel puts the entry(24:00) as 00.00 so the
formula reads it as zero hour, hence the equation calculates as 00:00 -
16:00 = -16.00.

The sheet is used for scheduling and is printed and posted. Getting excel
to display (and calculate 24:00) would be a big help.

Thanks in advance

Ok..., this works. Thanks.

But:-)

In order to make calculations, i.e. sum totals of the product cells, we need
to keep the formats in time, i.e. 8:30 + 8:45 = 17:15 . What I'm trying to
do is convert the time formats into number formats. So that total time 8hours
and 30 minutes (8:30) results in a number 8.5.

You forgot to quote the relevant bits of the message to which you were
trying to reply, but if you want to convert 8:30 to 8.5, multiply by 24 and
format the result as general or number, not as time.
--
David Biddulph

DaveB wrote:
Ok..., this works. Thanks.

But:-)

In order to make calculations, i.e. sum totals of the product cells,
we need to keep the formats in time, i.e. 8:30 + 8:45 = 17:15 . What
I'm trying to do is convert the time formats into number formats. So
that total time 8hours and 30 minutes (8:30) results in a number 8.5.

Thanks Dave, got it to work. Sorry about the non quote.

Another question. Why does this work?

thanks

"David Biddulph" wrote:

You forgot to quote the relevant bits of the message to which you were
trying to reply, but if you want to convert 8:30 to 8.5, multiply by 24 and
format the result as general or number, not as time.
--
David Biddulph

DaveB wrote:
Ok..., this works. Thanks.

But:-)

In order to make calculations, i.e. sum totals of the product cells,
we need to keep the formats in time, i.e. 8:30 + 8:45 = 17:15 . What
I'm trying to do is convert the time formats into number formats. So
that total time 8hours and 30 minutes (8:30) results in a number 8.5.

It works because Excel dates and times are stored in units of 1 day, and
there are 24 hours in a day.
--
David Biddulph

DaveB wrote:
Thanks Dave, got it to work. Sorry about the non quote.

Another question. Why does this work?

thanks

"David Biddulph" wrote:

You forgot to quote the relevant bits of the message to which you
were trying to reply, but if you want to convert 8:30 to 8.5,
multiply by 24 and format the result as general or number, not as
time. --
David Biddulph

DaveB wrote:
Ok..., this works. Thanks.

But:-)

In order to make calculations, i.e. sum totals of the product cells,
we need to keep the formats in time, i.e. 8:30 + 8:45 = 17:15 .
What I'm trying to do is convert the time formats into number
formats. So that total time 8hours and 30 minutes (8:30) results in
a number 8.5.