View Single Post
  #10  
Old June 2nd, 2010, 09:25 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default Calculate the amount of time over a permitted amount (12 hours

But d:h:mm will have problems when you go beyond 31 days.
--
David Biddulph


"Mike H" wrote in message
...
Hi,

If the formula don't work for hours in excess of 24 then you havent
formatted the cells as shown. Format as

[h]:mm

The square barckets stop rollover after 24 hours. If you want days then
format as

d:h:mm
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Steve M" wrote:

Hi
Both of the solutions above work within a 24 hour period, but some jobs
can
go over by a few days so I need it to show this in either hours or days
and
hours.
many thanks do far

"Mike H" wrote:

Hi,

What do you want to see if it takes less than or equal to 12 hours?
This
displays the text "In Time"

=IF((L1-C1)*24=12,"In Time",(L1-C1)-TIME(12,0,0))

format this cell with
[h]:mm
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"Steve M" wrote:

Hi
I would like to calcualte the time over a permitted amount and
display it in
a cell.

Column C is the start time dd/mm/yy hh:mm and Column L is the finish
time
dd/mm/yy hh:mm. The alloted time is 12 hours for the job and and
need column
M to show the amount of time used over the 12 hours.

Many Thanks.