On Monday June 04 2007 1:49 pm, David M wrote: > I'm trying to create a timesheet in Calc where I enter my start and > end times for each day of work (in 24-hour format to avoid tying > myself in knots with the 12-hour time format), and then have Calc > show the amount of time worked for each day (ie, calculate the > difference between the two times, and also subtract my lunch > break), and then add up those daily values for weekly totals. > > I defined the format of the appropriate cells as "Time", and so > far, so good ..apart from when I work more than 24 hours in a week > (my level of work varies) and then my attempted weekly total Does > The Wrong Thing and carries away 24 hours-worth [1], leaving only > the "remainder", and so displaying the wrong answer. > > [1] to who knows where.. > > I presume, therefore, that Time is strictly a time-of-day format, > rather than that to be used for performing calculations on > hour:minute values? > > Is it possible to set an appropriate cell format that will allow me > to add up these time values to get the correct cumulative total? > > The only solution that springs immediately to mind would be just to > use normal decimal numbers (and to enter the time in decimal > format, ie, 18:30 -> 18.5, fortunately I don't require greater than > quarter-hour accuracy and can avoid too-messy fractions), but that > would still be seriously ugly, and I just /know/ I'd end up typing > (eg) 18.30 without thinking, half the time.. :-( > > Thanks in advance for any advice, > > David.
It is all in how you format your time. What you need to do in your time cells is to create a custom format. Look at the Format code in the Options section at the bottom. With a 24 hour clock, you should see: HH:MM or HH:MM:SS. To do what you want to do, change it to [HH]:MM or [HH]:MM:SS. Click the green check to the right, and then Click OK. Dan --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
