1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Help Needed on Excel Time Function

Discussion in 'Business Applications' started by coachdan32, Jul 20, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. coachdan32

    coachdan32 Thread Starter

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    I am trying to automate an Excel spreadsheet to calculate time worked. The sheet is attached and I am only trying to get it done on the first persons time section. They will enter their time in military format. My need is to get it were it will calculate, in the Hours Worked section, their time (minus lunch) in a H:MM format.

    My secondary hope is to get it to look at the minutes in the H:MM calculation and compare it to the Minutes field in the 100Minute Clock tab and convert the :MM portion to the column on the far right. I don't know if this is possible or not, but it is my ideal solution. It must look exactly like it does now (no row/column additions or subtotals can be visible). I have automated the ABS Code portion for the most part with the addition of the Formula tab, which I will hide before rollout, but I am stumped on the time totals.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,120
    First Name:
    Wayne
    are you after decimal parts of hour or just the mins
    and if the only do say 23mins for lunch will you still deduct an hour or just 23mins
    however, if they are out 1hr 23min do you want to deduct 1hr 23min
     
  3. coachdan32

    coachdan32 Thread Starter

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Just the minutes, and I want it to deduct exactly the amount of time taken for lunch. Thanks for the quick response!
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,120
    First Name:
    Wayne
    if you use custom format
    hh:mm

    then enter as
    09:30
    13:00

    etc
    then
    =(D6-D3)-(D5-D4)
    should work
     
  5. coachdan32

    coachdan32 Thread Starter

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Great! I got that part working. Is there anyway to incorporate the 100 minute clock part into it?
     
  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,120
    First Name:
    Wayne
    if you change the formula to this
    =(((D6-D3)-(D5-D4))-INT(((D6-D3)-(D5-D4))))*24
    then it will return the decimal part of the mins
    BUT does not use your table
    but looking at your table its the same as mins/60 to 2 decimal places - so my formula should give the same result as you table
     
  7. coachdan32

    coachdan32 Thread Starter

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Works GREAT!! Thanks for your help.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,120
    First Name:
    Wayne
    your welcome - check it out for all conditions as I have not
    (y)
    Would you now mark this thread solved - which you have the power to do from your first post.
    goto
    Thread tools>
    Tick - Mark Solved

    Thanks (y)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/484850

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice