+ Reply to Thread
Results 1 to 5 of 5

convert decimal numbers to a fraction of an hour for payroll hour

  1. #1
    Flower
    Guest

    convert decimal numbers to a fraction of an hour for payroll hour

    need to convert payroll hours to 1/4 of an hour.

    e.g 123.56 convert to 123 hrs and 45 minutes
    123.78 convert to 124 hours
    decimals of 0-25 = 15 minutes
    26 - 50 = 30 minutes
    51 - 75 = 45 minutes
    76 - 99 = 60 minutes

    using office 2000

  2. #2
    Bob Phillips
    Guest

    Re: convert decimal numbers to a fraction of an hour for payroll hour

    Use

    =CEILING(A1,0.25)

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "Flower" <[email protected]> wrote in message
    news:[email protected]...
    > need to convert payroll hours to 1/4 of an hour.
    >
    > e.g 123.56 convert to 123 hrs and 45 minutes
    > 123.78 convert to 124 hours
    > decimals of 0-25 = 15 minutes
    > 26 - 50 = 30 minutes
    > 51 - 75 = 45 minutes
    > 76 - 99 = 60 minutes
    >
    > using office 2000




  3. #3
    Flower
    Guest

    Re: convert decimal numbers to a fraction of an hour for payroll h

    This was pretty good to know for future. thanks
    I am still unsure how to do my formula for this. Let me explain it again.
    the way this company uses the payroll round of minutes is as follows
    8.13 to 8.37 = between .13 and .37 is considered 15 minutes
    between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
    ..63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour

    the hours are there but the numbers after the decimal points are rounded to
    the equilant 15, 30, 45 or 1 hour.

    so how do you set the formula for the change only at the decimal levels to
    either of the four rounded minutes.

    If you can help answer this, I would really appreciate.

    Thanks


    "Bob Phillips" wrote:

    > Use
    >
    > =CEILING(A1,0.25)
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "Flower" <[email protected]> wrote in message
    > news:[email protected]...
    > > need to convert payroll hours to 1/4 of an hour.
    > >
    > > e.g 123.56 convert to 123 hrs and 45 minutes
    > > 123.78 convert to 124 hours
    > > decimals of 0-25 = 15 minutes
    > > 26 - 50 = 30 minutes
    > > 51 - 75 = 45 minutes
    > > 76 - 99 = 60 minutes
    > >
    > > using office 2000

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: convert decimal numbers to a fraction of an hour for payroll h

    How about, assuming that all the times are in A2:A20, then

    =SUMPRODUCT(ROUND(A2:A20*4,0)/4)

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "Flower" <[email protected]> wrote in message
    news:[email protected]...
    > This was pretty good to know for future. thanks
    > I am still unsure how to do my formula for this. Let me explain it

    again.
    > the way this company uses the payroll round of minutes is as follows
    > 8.13 to 8.37 = between .13 and .37 is considered 15 minutes
    > between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
    > .63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour
    >
    > the hours are there but the numbers after the decimal points are rounded

    to
    > the equilant 15, 30, 45 or 1 hour.
    >
    > so how do you set the formula for the change only at the decimal levels to
    > either of the four rounded minutes.
    >
    > If you can help answer this, I would really appreciate.
    >
    > Thanks
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Use
    > >
    > > =CEILING(A1,0.25)
    > >
    > > --
    > >
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from the email address if mailing direct)
    > >
    > > "Flower" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > need to convert payroll hours to 1/4 of an hour.
    > > >
    > > > e.g 123.56 convert to 123 hrs and 45 minutes
    > > > 123.78 convert to 124 hours
    > > > decimals of 0-25 = 15 minutes
    > > > 26 - 50 = 30 minutes
    > > > 51 - 75 = 45 minutes
    > > > 76 - 99 = 60 minutes
    > > >
    > > > using office 2000

    > >
    > >
    > >




  5. #5
    Flower
    Guest

    Re: convert decimal numbers to a fraction of an hour for payroll h

    hi,

    It still did not work.

    ok, this how the spreadsheet is being kept from the time card being punched

    Date In Out hrs1 In out hrs 2 TTL Hrs
    Worked rounds to
    2/1/06 8.57 13.57 5.0 14.27 17.03 2.76 7.76
    7.45
    2/2/06 8.25 13.12 4.87 13.63 17.00 3.37 8.24
    8.15
    2/3/06 8.00 12.32 4.32 13.10 17.50 4.40 8.72
    8.45

    Total hours for 3 days 24.72

    Rounding is bet. .13 -.37 = .15 minutes
    .38 - .62 = .30 minutes
    .63 - .87 = .45 minutes
    .88 - .12 = 1.00 hour

    Since hours change each week the formula should automatically calculate
    based on the rounding which could sometimes be 1st category or 2nd category.
    maybe and if statement should work, i am not sure


    "Bob Phillips" wrote:

    > How about, assuming that all the times are in A2:A20, then
    >
    > =SUMPRODUCT(ROUND(A2:A20*4,0)/4)
    >
    > --
    >
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from the email address if mailing direct)
    >
    > "Flower" <[email protected]> wrote in message
    > news:[email protected]...
    > > This was pretty good to know for future. thanks
    > > I am still unsure how to do my formula for this. Let me explain it

    > again.
    > > the way this company uses the payroll round of minutes is as follows
    > > 8.13 to 8.37 = between .13 and .37 is considered 15 minutes
    > > between .38 to .62 (e.g. 8.52) is considered 30 minutes (8 1/2 hours)
    > > .63 to .87 is considered 45 minutes and .88 to .12 is considered 1 hour
    > >
    > > the hours are there but the numbers after the decimal points are rounded

    > to
    > > the equilant 15, 30, 45 or 1 hour.
    > >
    > > so how do you set the formula for the change only at the decimal levels to
    > > either of the four rounded minutes.
    > >
    > > If you can help answer this, I would really appreciate.
    > >
    > > Thanks
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Use
    > > >
    > > > =CEILING(A1,0.25)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > Bob Phillips
    > > >
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > > "Flower" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > need to convert payroll hours to 1/4 of an hour.
    > > > >
    > > > > e.g 123.56 convert to 123 hrs and 45 minutes
    > > > > 123.78 convert to 124 hours
    > > > > decimals of 0-25 = 15 minutes
    > > > > 26 - 50 = 30 minutes
    > > > > 51 - 75 = 45 minutes
    > > > > 76 - 99 = 60 minutes
    > > > >
    > > > > using office 2000
    > > >
    > > >
    > > >

    >
    >
    >


+ 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