# Thread: Timesheet that subtracts lunch hours? (2000)

1. ## Timesheet that subtracts lunch hours? (2000)

I've seen quite a few examples of time calculations here, but none that address my particular issue. I have a simple time-in-time-out time sheet. However, it has a cell for the amount of time used at lunch (in minutes). Although this cell displays minutes, it's actually a specific time (e.g. 0:30 is really 12:30:00 AM). The calculation takes the end time, then subtracts the beginning time and lunch time. It works but I'm real uncomfortable with the weird lunch time recording. I've tried to find a format that just shows minutes, but have had no luck.

Someone give me a whack on the side of the head, please!

2. ## Re: Timesheet that subtracts lunch hours? (2000)

Excel doesn't distinguish between clock time and time as duration - both are stored the same way, and it works well in calculations, so there is no need to change anything.

If you would still prefer to show just an amount of minutes, use General or Number format. You must then subtract minutes / 1440 from (end time - start time). Excel stores all dates and times using 1 day as unit, and there are 24*60 = 1440 minutes in 1 day.

3. ## Re: Timesheet that subtracts lunch hours? (2000)

I'm not sure I'm visually your description right, but it sounds like you're facing the same problem I went through coming up with a timesheet in Excel. So I've attached one for you to look at, if it might help. Our staff enters their in-out time like 8:00 am, 12:00 pm, 1:00 pm, 5 pm. There's extra space if the person must leave an additional time during the day like for an appointment or to run an errand. At the end of the day, the user looks at the Time column and enters the time translated into a number. For example, if the Time column read 8:34 (8 hours and 34 minutes), the person would enter 8.5 in the Hours Worked column. (The staff submits time broken down into 15 minute increments, e.g. 8, 8.25, 8.5, 8.75, 9, etc.) At the end of the week there's a place under the Over/Short columns for how much time they were over or short for the week. They look at that number and break it down appropriately in the columns on the right for Personal, Vacation, etc. In the end, the value of the yellow cells must match. I hope that helps you out.

4. ## Thanks!

I guess I'll just have to live with the weird lunch hour formatting. So long as it doesn't screw up the calculations, it'll do the trick.

Hans, you must monitor this site every waking moment. It astonishes me how quickly you respond to my requests. If I ever make it back to Holland, I'm buying you a pitcher of beer.

Ailios, having had to use a score of time sheets over the years, it amazes me how many different ways there are to perform the same basic function. I like the layout you use. Unfortunately, I'm stuck with my client's layout but am determined to use Excel for what it is intended: calculations. You see, my client's spreadsheet requires that we manually calculate how many hours we work each day! Can you believe it? My simple equation is going to blow them away. Heh, heh!

5. ## Re: Thanks!

You can format the cells with the lunch time duration the way you want. The formula bar will still display clock time, but the spreadsheet itself should look OK.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•