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: excel forumula question

Discussion in 'Business Applications' started by mattig89ch, Apr 13, 2015.

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

    mattig89ch Thread Starter

    Joined:
    Dec 31, 1969
    Messages:
    398
    Hidy ho all,

    My job has me entering my clock-in times on an excel spread sheet. I then figure out exactly how long the day was, and submit that time sheet to my superior who checks my work and passes it on to god knows where.

    Not exactly a difficult task, but I thought it would be awesome to figure out a forumula to have excel do this automatically.

    I've gotten it most of the way there, but I'm having trouble converting the time I worked to decimals.

    my formula is: (ShiftEnd - ShiftStart)-(BreakEnd-BreakStart). This produces the hours I worked in time format, but when I try to convert it to decimal it comes out to some weird decimal thats less then 1. Like .3 or something. I wanted it to come to a nice decimal place.

    For example, if I work from 9AM to 5PM and take a 15 minuted break, I want the result to be 7.75 hours. Right now it resolves to 7:45, correct, but not what I'm looking for. How do I get this result to be 7.75 automatically?
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    multiply by 24
    and format the cell as decimal
     
  3. mattig89ch

    mattig89ch Thread Starter

    Joined:
    Dec 31, 1969
    Messages:
    398
    lol, was about to post that myself.

    Thanks etaf!

    The formula is =((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24. make sure the cell you type this formula in is set to a decimal format. Otherwise you get some weird results.

    One more question for the community, is there anyway to get excel to put " Hours" or " hrs" after the result?
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    =(((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24)&" Hrs"
    =(((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24)&" Hours"

    But that will change to text and so will not add up
    you may need to format

    =ROUND((((ShiftEnd-ShiftStart)-(BreakEnd-BreakStart))*24),2)&" Hours"
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just select custom format for the cells
    0.0 " Hrs"
    that way it shows up as text but remains numeric and can be used in calculations
     
  6. mattig89ch

    mattig89ch Thread Starter

    Joined:
    Dec 31, 1969
    Messages:
    398
    Thanks for the help all. I have one last question, then I think I'll be good.

    I want to put an if test in there, so that when i enter my time coming in, but not my time leaving, if the result is less then 0 set the cell = 0. unfortunately, the formula wizard is throwing errors. and I can only get it to have a 0 in there is I do the whole thing > 0.

    My idea is something like this:
    If((((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs" < 0, 0, (((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs"))

    Edit: srry if all those parens are getting hard to follow. I'm a little lost by them myself.
     
  7. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    If((((EndTime-StartTime)-(BreakEnd-BreakStart))*24) < 0, 0, (((EndTime-StartTime)-(BreakEnd-BreakStart))*24)&" hrs"))
    remove the Hrs as that turns the result to text as mentioned
     
  8. 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/1146532

  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