Prev: Calculate the amount of time over a permitted amount (12 hours)
Next: count between a date range
From: "David Biddulph" groups [at] on 2 Jun 2010 16:25
But d:h:mm will have problems when you go beyond 31 days.
--
David Biddulph


"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:444902C0-2A03-4481-93FB-AF2B0AFB2CF4(a)microsoft.com...
> 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.


First  |  Prev  | 
Pages: 1 2
Prev: Calculate the amount of time over a permitted amount (12 hours)
Next: count between a date range