+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : How to convert ex: (8-4:30) into total hours

  1. #1
    Registered User
    Join Date
    12-05-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    How to convert ex: (8-4:30) into total hours

    Hi,
    I made a simple worksheet that we have been manually entering the time for employees, but there are too many errors (even with simple math). Can someone help me convert the time of ex: written 8-4:30 or 10-3 (meaning 8:00am to 4:30pm) where you have the total weekly hours? Right now I have a column for overtime. Is there a way to automatically calculate the over time also? The work day is 8-4:30 with a half hour lunch (lunch is not calculated into the hours, so you minus a half hour). 8-4:30 equals 8 hours. So, if a person works 8am to 6:30pm, how can I set it up where in the first row of time, I will type in 8-6:30pm. In the totals column to the far right, it will display 8 hours. In the (O.T.) column, it will display 2 hours.

    This way, when I call in my payroll, I will have the total "regular" hours in one row and underneath, I will have the total overtime hours.

    Or... does anyone have a better solution to keeping track of their hours?

    Thanks!
    Marty
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to convert ex: (8-4:30) into total hours

    Hi,

    I'd advise that you capture your times with a time format that excel can calculate. At the moment you're entering text which is not very useful and doesn't lend itself to easy analysis.

    First decide whether you want users to enter hours and minutes or hours and decimals of an hour. i.e. 8:30 or 8.5 and create cells for both start & finish times.

    Assuming you choose hours & minutes then use cell data validation and set the minimum and maximum times, (could be 0:00 to 23:59) or any other control you want, then you can prevent someone entering an invalid time.

    Once you have proper time 'numbers' then you can use these in calculations.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-05-2010
    Location
    NY
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: How to convert ex: (8-4:30) into total hours

    Hi,
    Actually, it doesn't matter how I write it, but I understand what you're saying. Right now, we just enter it exactly as: 8-4:30 or 10-6:30, etc, but as long as it's consistent, I don't mind how it's coded.

    We really don't use partial hours (8:20, 9:15). It's only hour and half hour time increments.

    Marty

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    I'd advise that you capture your times with a time format that excel can calculate. At the moment you're entering text which is not very useful and doesn't lend itself to easy analysis.

    First decide whether you want users to enter hours and minutes or hours and decimals of an hour. i.e. 8:30 or 8.5 and create cells for both start & finish times.

    Assuming you choose hours & minutes then use cell data validation and set the minimum and maximum times, (could be 0:00 to 23:59) or any other control you want, then you can prevent someone entering an invalid time.

    Once you have proper time 'numbers' then you can use these in calculations.

    Regards

  4. #4
    Registered User
    Join Date
    07-10-2008
    Location
    West Palm Beach
    MS-Off Ver
    2007
    Posts
    41

    Re: How to convert ex: (8-4:30) into total hours

    Richard,

    I'm attaching a copy of a timesheet that we use here at my office. If you look at the far left, you'll see where we are able to view the totals for the week. I've taken all sensitive or personal data out of the worksheet. Hope this helps you. If you have questions just ask.
    Attached Files Attached Files

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to convert ex: (8-4:30) into total hours

    Hi

    See the attached.

    In C10:D10 I have added a data validation that ensures you enter a time in hours:minutes.
    P10 is then simply the difference between the two less 0.5.

    The overtime in E10 is therefore P10-8

    You don't need the Minutes columns from column F onwards.

    HTH
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1