+ Reply to Thread
Results 1 to 5 of 5

Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

  1. #1
    Registered User
    Join Date
    02-10-2010
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    22

    Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

    Hello, I am using Excel 2010.

    I don't use spreadsheets much, and when I do design them, they are fairly basic, rarely complicated. There is something I want to do with one, though, and am requesting assistance, please.

    I'm creating a payroll history spreadsheet for myself, to keep track of my hours, overtime, gross wages, etc. Nothing fancy.

    I'd like to know if I can enter my hours into a cell, and have Excel automatically deduct my lunch time if I'm over 8 hours.

    For example, if I type 6-2:30 in a cell, can Excel automatically see it is more than eight hours ? And then automatically add 8 hours to the Total Hours cell, instead of 8 1/2?

    Here are two screen shots of what I want to do.

    excel1.jpg

    excel2.jpg

    Is this possible? Can I get Excel to automatically determine 6-2:30 is a set of hours, determine it's 8 1/2, and if over 8, deduct .5 hours? What's an easy way to do this, if possible?

    If you need more information from me, please request it. I'll do my best to provide further details. Please consider me an Excel novice. Thank you! Jd

  2. #2
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    896

    Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

    There are 2 ways to do it.

    First is to insert your time in 24 hr format, this way it'll be easier to extract the information (assuming if you'd like to input the number of hours in 1 cell)

    Second way is to have start and end time in each cell...i.e. column B will be start time and column C will be end time (column A will be your date)

  3. #3
    Registered User
    Join Date
    02-10-2010
    Location
    New Jersey
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

    Thank you for replying.

    It didn't even occur to me to have an IN and OUT column, like time clocks with time cards. That will double the amount of columns for the work week, but if it ultimately makes the calculations easier, I'll go for it.

    But I still can't figure out how to get Excel to calculate the 8-hour day if it's over 8 hours.

  4. #4
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    896

    Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

    Hello jdanniel,

    If you want, I can create a simple "template" for you.

    But I would like to know a little info from you, such as will you work after hours...in a sense, you begin at 11pm and end at 2am etc?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,076

    Re: Can Excel Deduct 30 Minutes from an 8 1/2 Hour Cell?

    jdanniel, just remember, excel treats hours as fractions/decimals of a day, even though it displays then as time. So 8 hours (or 08:00 AM) is really 8/24 or 0.333 of a day. 6 hours is 0.25, 16 hours (or 04:00 PM) is 0.666...and so on
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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