time calculation, 24:00 - 16:00 = 8

[DaveB]

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

 

[Chip Pearson]

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)

 

[John]

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]

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.

 

[David Biddulph]

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.

 

[DaveB]

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

Another question. Why does this work?

thanks

 

[David Biddulph]

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