Calculate the amount of time over a permitted amount (12 hours)
in [General]
|
Prev: Can I increase the weight of a font in Excel?
Next: Calculate the amount of time over a permitted amount (12 hours
From: Steve M on 2 Jun 2010 06:16 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:27 =MAX(L2-C2-"12:00",0) and format as time,[h]:mm -- HTH Bob "Steve M" <SteveM(a)discussions.microsoft.com> wrote in message news:909E493A-ED55-40A3-9406-6588A333F281(a)microsoft.com... > 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: Ms-Exl-Learner on 2 Jun 2010 06:35 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! -------------------- (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.
From: Mike H on 2 Jun 2010 06:41
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. |