From: "David Biddulph" groups [at] on 2 Jun 2010 16:25 But d:h:mm will have problems when you go beyond 31 days. -- David Biddulph "Mike H" wrote in message news:444902C0-2A03-4481-93FB-AF2B0AFB2CF4(a)microsoft.com...> 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. First  |  Prev  |