Rounding up hours
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Rounding up hours

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I use a spreadsheet to calculate labour time for engineers etc. and we calculate all our work to 4 hour multiples.

    For example a 3 hour job indreases to 4 hours, an 11 hour job rounds up to 12 and a 12.8 hour job rounds up to 16.

    I am in need a formula that will always round up to the nearest four hours using the following data.

    In G6 I have a Subtotal of the labour hours
    In F9 I have an allowance (hours for awkward access etc)
    In F10 I have an allowance for excessive travelling (ie 4 hours driving to complete a 1 hour job)

    I want to put the roundup formula in F11 and the overall total hours are displayed in G4.

    Someone gave me the formula

    =IF(G6+F9+F10>0, ((TRUNC((G6+F9+F10)/4)+1)*4)-G6-F9-F10, 0)

    It sort of works but if I add sufficient hours in any of my allowances to make the total divisible by 4 the formula still adds another 4 hours?

    This is totally beyond me and I hope I have explained it correctly?

    All help would be welcomed. VBA code or formula.

    Many thanks in anticipation

    Dave

    [ This Message was edited by: daveray on 2002-03-28 04:33 ]

    [ This Message was edited by: daveray on 2002-03-28 04:34 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I understood you correctly, you should use:

    =G6+F9+F10 in G4

    and

    =CEILING(G4;4) in F11

    Did I?

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Almost but it was I that made the mistake.

    I wrote
    In G6 I have a Subtotal of the labour hours
    In F9 I have an allowance (hours for awkward access etc)
    In F10 I have an allowance for excessive travelling (ie 4 hours driving to complete a 1 hour job)

    I want to put the roundup formula in F11 and the overall total hours are displayed in G4.

    Someone gave me the formula

    =IF(G6+F9+F10>0, ((TRUNC((G6+F9+F10)/4)+1)*4)-G6-F9-F10, 0)

    It sort of works but if I add sufficient hours in any of my allowances to make the total divisible by 4 the formula still adds another 4 hours?
    But I forgot to include the following.

    The total "extra hours" are summed in G12 using =sum(F9:F11) and to get my total hours I simply put the total in G14 using = G6+G12

    Would it be easier of I sent you a copy of the sheet?

    Thanks for your reply

    Dave

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Little Italy
    Posts
    93
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, please send me a copy of your Sheet, but I hope you are not in hurry cause I got to run now and I'm able to return to this issue no earlier than tomorrow, sorry man!
    I'll be glad to take a look at it then, though!

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I'll try and help you if you want to send me a copy?
    Tom
    tstom@hotmail.com

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Put this formula where you need it to go.
    Will round total upto the next four.
    Will leave the total alone if it is a multiple of four.
    =IF(MOD((F9+F10+G6),4)<>0,CEILING(F9+F10+G6,4),(F9+F10+G6))
    Have a Nice Day!
    Tom

    [ This Message was edited by: TsTom on 2002-03-28 06:36 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    one more offering
    =ROUNDUP(SUM(G6,F9:F10)/4,0)*4

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I believe you have a basic misunderstanding of number systems. If you change your way of thinking the solutions to this, and future problems of this sort, will be much easier. Lets examine your number system a little more carefully.

    You are trying to work in 4-hour increments, but your calulations are all based on a number system based on 1 hour increments. Your 1 hour increments are using the base 10 arithmetic that we have all grown up with. what you need is a base 4 number system and your problems will go away.

    For example, lets make up a new base type called fhi (four hour increments). to convert from 1 hour increments to fhi, you simply divide by 4, ie, 12.8 hours = 12.8/4 = 3.8 fhi. as you can now see, the fhi number will work with the functions ceiling,
    floor, average, +,-,*,/, etc. Working only with cells that are of fhi format, do all your calculations. when you are finished, you
    can then convert back to 1 hour increments, ie, =ceiling(3.8fhi) = 4fhi*4=16 hours.

    - leslie

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Leslie

    Your comments about thinking were most welcome. I have a fair understanding of Maths and number bases but I am dreadful at translating that into formulae that Excel understands. Unfortunately I know nothing about the CEILING function and my knowledge of Excel is limited to the basic functions.

    I am learning though through this board and your idea was excellent - thanks I always like the self help approach!

    Dave


    [ This Message was edited by: daveray on 2002-03-28 10:28 ]

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry to but in,

    the help file for CEILING is:

    Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.

    Syntax

    CEILING(number,significance)

    Number is the value you want to round.

    Significance is the multiple to which you want to round.

    Remarks

    If either argument is nonnumeric, CEILING returns the #VALUE! error value.
    Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.
    If number and significance have different signs, CEILING returns the #NUM! error value.

    Examples

    CEILING(2.5, 1) equals 3

    CEILING(-2.5, -2) equals -4

    CEILING(-2.5, 2) equals #NUM!

    CEILING(1.5, 0.1) equals 1.5

    CEILING(0.234, 0.01) equals 0.24

    If you don't understand a Function ALWAYS try the help files first, they're can be more informative than any advice on the board, the reason I generally find is that people are nearly always saying the same as the help file but in a longer way (I'm VERY guilty of this myself).

    If a help file doesn't do then it's time for the Experts on here.


    "Have a good time......all the time"
    Ian Mac

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com