| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula to calculate amount of hours downtime spent in days | Excel Worksheet Functions | |||
| How do I get total amount of hours in time format | Excel Discussion (Misc queries) | |||
| CALCULATE GST AMOUNT FROM AMOUNT IN OTHER CELL | Excel Worksheet Functions | |||
| How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
| How do I calculate Amount of Sales Tax from Total Amount? | Excel Worksheet Functions | |||
Linear Mode
