Time card function

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Time card function

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

    Default

     
    I am repeating the question since I did not see it posted to the board.

    I would like to create a time card sheet that has time in and time out, then caculate the number of hours worked (this I can do) then convert the mm to 1/4 hour increments i.e. 0 to 15 minutes = .25, 16 to 30 minutes = .5, 31 to 45 minutes = .75, 46 to 60 minutes = next whole hour.

    Even better if we can incorporate the 8 minuite rule (7 min or less is rounded down and 8 min or greater is round up to the next 1/4 hour.

    I will take the output of this function and multply by the pay rate to get gross pay.

    Thanks for the help, Curt

    [ This Message was edited by: c24c4 on 2002-06-01 06:01 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    St. Louis
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    St. Louis
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I built a spreadsheet years ago in Lotus.
    I addressed your problem of the "7 minute" rule via a lookup table. The file can be loaded into Excel where you can examine the formulas.

    Although there are macros in it to control pointer movement, they are not necessary to accomplish what you want and can be ignored.

    My email: droodhall@earthlink.net

    Dexter

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dexter you did not attach the lookup table if that was your intent.

    Curt

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to create a time card sheet that has time in and time out, then caculate the number of hours worked (this I can do) then convert the mm to 1/4 hour increments i.e. 0 to 15 minutes = .25, 16 to 30 minutes = .5, 31 to 45 minutes = .75, 46 to 60 minutes = next whole hour.
    What is the end format of your #of hours calculation (for example is 8 hours 15 minutes showing as 8:15 (time format) or 8.25 (number format))

    If number format (8:13 = 8.216667)

    =ROUND(C4*4,0)/4

    or if a time format (8:13 = 8:13:00 AM)

    =ROUND(C4*96,0)/(96)

    Where C4 holds your existing hour calculation.

    [ This Message was edited by: Asala42 on 2002-06-01 06:39 ]

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is in time format hh:mm since I amusing the 24 hour format for the time card entries
    and the caculation.
    I could not seem to get a reasable number formate out of the function.

    curt

    [ This Message was edited by: c24c4 on 2002-06-01 07:04 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It is in time format hh:mm since I amusing the 24 hour format for the time card entries
    and the caculation.
    I could not seem to get a reasable number formate out of the function.
    You can convert between the 2 formats using a multiplier of 24 (time format 8:15*24 = 8.25 in a number format)

    Did something go wrong with the above formula? If so, specify your formula and the results.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,443
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default

    Microsoft Excel - aaTimeCard c24c4.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    =

    A
    B
    C
    D
    E
    F
    G
    1





    $6.00
    2
    Time InTime OutHours workedRoundedDecimal timeGross payAll in 1 formula
    3
    8:3014:205:505:455.75$34.50 $34.50
    4
    9:0014:105:105:155.25$31.50 $31.50
    5
    9:0013:044:044:004$24.00 $24.00
    6
    10:0015:285:285:305.5$33.00 $33.00
    Sheet1

    To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


    [ This Message was edited by: Aladin Akyurek on 2002-06-01 08:14 ]

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi c24c4:

    For rounding the time to your specification, you can use the MROUND function, let us say Start time is 8:30 and Leave Time is 14:20

    then =MROUND("14:20"-"8:30","0:15") ... will give you 5:45 -- formated as Number 5.75

    and if StartTime is 8:30 and LeveTime is 14:23

    then =MROUND("14:23"-"8:30","0:15") ... will give you 6:00, and formatted as number 6.0

    Of course you should enter the StartTime and LeaveTime as Cell references. Also please note that to be ble to use the MROUND function, you have to have the Analysis ToolPak checked through TOOLS|ADD_INs

    Regards!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  10. #10
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks to all for all the help, it was more than expected.

    Spl thanks to Aladin Akyurek and Yogi Anand

    Curt

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