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

D

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
 
C

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)
 
J

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
 
D

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.
 
D

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.
 
D

DaveB

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

Another question. Why does this work?

thanks
 
D

David Biddulph

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top