+ Reply to Thread
Results 1 to 4 of 4

Calculating staff roster time to hours, depending on time worked.

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Calculating staff roster time to hours, depending on time worked.

    Hey,
    I am quite new to excel but need to use it for some work reports and rosters. I have read over 100 forums but still can’t find help for what I am looking to do.
    I need a formula that will work out how many hours a staff member worked. I know that if I do the first formula in the document it works out the exact hours for me. But I need a formula that will work out if the answer is over 6 hours worked it will take 1 hour off but if it is under 6 hours it will show the amount of hours without taking any time off (this needs to all be done in the same cell some how). I could do it if I was able to use 4 cells but I can only use 3. After 3 or so hours I somehow came up with a complicated way to work out how to take an hour off if it is over 6 hours but if it is under 6 hours the cell stays blank. That is probably confusing so I have attached an example of what I have got and what I need to do.
    If it is at all possible any help would be much appreciated.
    Book1.xls

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Calculating staff roster time to hours, depending on time worked.

    Hello
    Take a look at the table 'Possible Solution?' in the attached reply workbook. Perhaps this might do as you require.

    Hope this helps.
    DBY
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Calculating staff roster time to hours, depending on time worked.

    Hi
    Just needs a minor change to your formula:

    =IF((F3-E3)="","",IF((F3-E3)>TIME(6,0,0),(F3-E3)-TIME(1,0,0),F3-E3))

    Replace the last set of double quotes with "F3-E3" and that does the trick.
    Good luck.
    Tony

  4. #4
    Registered User
    Join Date
    05-16-2013
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Calculating staff roster time to hours, depending on time worked.

    Hey.
    Oh wow thank you so much. That is exactly what I needed.

+ 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