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