Calculate the amount of time over a permitted amount (12 hours)

  • Thread starter Thread starter Steve M
  • Start date Start date
S

Steve M

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.
 
I assume that your data has headers, so the data should start from 2nd Row.

Copy and paste the below formula in M2 cell.
=IF($L2-$C2<=TIME(12,0,0),"",($L2-$C2)-TIME(12,0,0))

Select the M Column and Do Right Click>>Format
Cells>>Number>>Category>>Custom>>Type>> copy and paste the below format or
type it.

[h]:mm:ss

And give Ok…

Remember to Click Yes, if this post helps!
 
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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Mine does just that.

--

HTH

Bob

Steve M said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Thankyou

Mike H said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top