 If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.  Calculate the amount of time over a permitted amount (12 hours)
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read

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

#1 June 2nd, 2010, 11:16 AM posted to microsoft.public.excel.misc
 Steve M external usenet poster Posts: 31 Calculate the amount of time over a permitted amount (12 hours)

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.
#2 June 2nd, 2010, 11:27 AM posted to microsoft.public.excel.misc
 Bob Phillips[_3_] external usenet poster Posts: 489 Calculate the amount of time over a permitted amount (12 hours)

=MAX(L2-C2-"12:00",0)

and format as time,[h]:mm

--

HTH

Bob

"Steve M" wrote in message
...
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.

#3 June 2nd, 2010, 11:35 AM posted to microsoft.public.excel.misc
 Ms-Exl-Learner external usenet poster Posts: 522 Calculate the amount of time over a permitted amount (12 hours)

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 ClickFormat
CellsNumberCategoryCustomType copy and paste the below format or
type it.

[h]:mm:ss

And give Okâ¦

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------

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

#4 June 2nd, 2010, 11:41 AM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 8,419 Calculate the amount of time over a permitted amount (12 hours)

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.

#5 June 2nd, 2010, 11:58 AM posted to microsoft.public.excel.misc
 Steve M external usenet poster Posts: 31 Calculate the amount of time over a permitted amount (12 hours

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.

#6 June 2nd, 2010, 11:59 AM posted to microsoft.public.excel.misc
 Bob Phillips[_3_] external usenet poster Posts: 489 Calculate the amount of time over a permitted amount (12 hours

Mine does just that.

--

HTH

Bob

"Steve M" wrote in message
...
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.

#7 June 2nd, 2010, 12:04 PM posted to microsoft.public.excel.misc
 Mike H external usenet poster Posts: 8,419 Calculate the amount of time over a permitted amount (12 hours

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.

#8 June 2nd, 2010, 12:15 PM posted to microsoft.public.excel.misc
 Ron Rosenfeld[_2_] external usenet poster Posts: 9 Calculate the amount of time over a permitted amount (12 hours

On Wed, 2 Jun 2010 03:58:01 -0700, 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

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.
#9 June 2nd, 2010, 12:19 PM posted to microsoft.public.excel.misc
 Steve M external usenet poster Posts: 31 Calculate the amount of time over a permitted amount (12 hours

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.

#10 June 2nd, 2010, 09:25 PM posted to microsoft.public.excel.misc
 David Biddulph external usenet poster Posts: 8,714 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.

 Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is Off HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Microsoft Office     Setup, Installing & Configuration     General Discussions Microsoft Word     New Users     Tables     Formatting Long Documents     Mailmerge     Page Layout     General Discussion Microsoft Excel     Setting up and Configuration     New Users     Worksheet Functions     Links and Linking     Charts and Charting     General Discussion Microsoft Access     New Users     Database Design     Running & Setting Up Queries     Setting Up & Running Reports     Using Forms     General Discussion Microsoft Outlook     Installation & Setup     Contacts     Fax     Calendar     General Discussion     Outlook Express Microsoft Powerpoint, Publisher and Visio     Powerpoint     Publisher     Visio About OfficeFrustration.com     About this forum

All times are GMT +1. The time now is 10:26 PM. - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top Copyright ©2004-2019 OfficeFrustration.