Prev: Calculate the amount of time over a permitted amount (12 hours)
Next: count between a date range
From: Steve M on 2 Jun 2010 06:58
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.
From: Bob Phillips on 2 Jun 2010 06:59
Mine does just that.

--

HTH

Bob

"Steve M" <SteveM(a)discussions.microsoft.com> wrote in message
news:44190074-9669-4C17-8A07-155C214BECAB(a)microsoft.com...
> 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.


From: Mike H on 2 Jun 2010 07:04
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.
From: Ron Rosenfeld on 2 Jun 2010 07:15
On Wed, 2 Jun 2010 03:58:01 -0700, Steve M
<SteveM(a)discussions.microsoft.com> 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

You will need to enter start and end times including the dates.
Then format the result as [h]:mm to allow to show hours more than 24.
From: Steve M on 2 Jun 2010 07:19
Thankyou

"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.
 |  Next  |  Last
Pages: 1 2
Prev: Calculate the amount of time over a permitted amount (12 hours)
Next: count between a date range