Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Tacoma, Washington, USA
    Posts
    431
    Thanks
    0
    Thanked 3 Times in 3 Posts

    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.
    Attached Files Attached Files
    Daisy

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Montreal, Quebec, Canada
    Posts
    261
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •