+ Reply to Thread
Results 1 to 4 of 4

Require formula to return value of hours worked in tenths.

  1. #1
    Registered User
    Join Date
    06-21-2012
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    2

    Require formula to return value of hours worked in tenths.

    I am working on an hour recording sheet that will be used by future interns at my place of work. I have hit a road block and want to make this spreadsheet as autonomous as possible.

    What I have so far is a setup where the user enters their start time, stop time, and lunch duration (all in military). Another cell then subtracts the start time and lunch duration from the stop time for example:

    "Start Time:7:00
    Stop Time: 14:00
    Lunch Duration: 0:30

    The function then takes 14:00-7:00-0:30=6:30"

    This is good and all but our emplyer requires us to enter our hours in tenths of an hour. For example:

    The table we use is:

    TIME MUST BE ENTERED IN TENTHS OF AN HOUR
    ___Minutes_______Tenths__________Minutes___________Tenths
    _____0-5_________0___________30-35___________0.5
    ____6-11________0.1___________36-41___________0.6
    ___12-17________0.2___________42-47___________0.7
    ___18-23________0.3___________48-53___________0.8
    ___24-29________0.4___________54-59___________0.9


    So the input of the above 6:30 would end up being 6.5.

    Does anyone know how to write a function that will return the hours in this form? Any help is much appreciated. Thank you!
    Last edited by ChuckFerrera; 06-21-2012 at 10:01 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Require formula to return value of hours worked in tenths.

    If you multiply the result by 24 and format the cell as General, you will get 6.5 (when 6:30 is time result).
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    06-21-2012
    Location
    Indiana
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Require formula to return value of hours worked in tenths.

    Thank you! That works, but we don't go to hundredths so this gives unneeded accuracy which will falsly reflect in the calculated earnings... I can't format the number to be just one decimal place either because it will cause 5.56 to be rounded to 5.6... :/ Any thoughts on that?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Require formula to return value of hours worked in tenths.

    You can use TRUNC()

    e.g =TRUNC(A1*24,1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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