Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12
  1. #1
    Senior Member skrlin's Avatar
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    562
    Member #
    1063
    What I'm looking to do is create a spreadsheet that will calculate a paycheck based on the times inputed on the days worked. Here's the catch: I want excel to round the minutes up or down to 15 min increments. It would round up 7 minutes and 30 seconds after each increment but I don't want to be that technical, instead it would round at 7 whole minutes
    Explanation:
    7:01 - 7:07 would get rounded down to 7:00
    7:08 - 7:14 would get rounded up to 7:15
    7:16 - 7:22 would get rounded down to 7:15
    7:23 - 7:29 would get rounded up to 7:30
    ...and so on.

    I can't figure out how to use the functions to round minutes out of the time format without splitting the time into hours and minutes in different cells, which I don't want to do. Any ideas ?

    I'm open to suggestions for other ways of doing this in PHP/MySQL, JavaScript (however sloppily). Any ideas ??
    - Brian

  2.  

  3. #2
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    From Excel directly, you could enter in a series of nested if statements. It's messy as all hell, but it will work.

    Something like this:
    Code:
    =IF(MINUTE(A1)>7,IF(MINUTE(A1)>22,IF(MINUTE(A1)>37,IF(MINUTE(A1)>52,0,45),30),15),0)
    That will create your minute value based on your interval. I'll leave it to you to tweak the rest to come up with the appropriate hour value (since there is a case after 52 where it differs, and I'm too tired to think of it.)
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  4. #3
    Senior Member skrlin's Avatar
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    562
    Member #
    1063
    Hmmm... I'll have a go at that tomorrow. Thanks for the quick reply.
    - Brian

  5. #4
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    You got it, buddy. Let me know how that works out.

    (By the way, that formula is correct. I checked it in Excel and C&Ped it.)
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  6. #5
    Member benbun's Avatar
    Join Date
    Mar 2004
    Posts
    51
    Member #
    5273
    Ok assuming your time (to be rounded is in A1), you could use the following.

    =IF(MOD(A1,(1/96))<(1/192),A1-MOD(A1,(1/96)),A1-MOD(A1,(1/96))+(1/96))

    if you want I can forward an example spreadsheet showing this in action.

  7. #6
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    benbun: could you upload it please? I'd like to see the example and understand the formula myself. I'm assuming MOD is the same as it is in VBScript (a remainder), but why you have numbers in fractions is semi-confusing to me.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  8. #7
    Member benbun's Avatar
    Join Date
    Mar 2004
    Posts
    51
    Member #
    5273
    I will upload the file once I get home as it is on my home PC.

    To understand the fractions you need to understand how Excel deals with times.

    Excel splits the day into seconds.
    Therefore one day has 24 * 60 * 60 = 86,400 seconds ( Hours x minutes x seconds ).

    Therefore 1/86400 = 1 second.

    Using this we can say 15 minutes = 15 x 60 = 900 seconds. To denote this in Excel we use 900 x 1/86400 = 900/86400.

    This simplifies to 1/96 and hence the mysterious fraction used in the formula.

    Hope this helps

  9. #8
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Ahhhhh yes. Makes sense now. I don't often use Excel; hence the question.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  10. #9
    Senior Member skrlin's Avatar
    Join Date
    Apr 2003
    Location
    Illinois
    Posts
    562
    Member #
    1063
    Well, I tried your formula a couple of different ways Adam, but to avail...but when I tried benbun's I just changed the cell number and it worked like a charm. And he even helped me to figure out my next question (about calculating overtime) with his next post. So I awarded benbun the TD. I appreciate the effort though. Thanks guys!
    - Brian

  11. #10
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    No harm, bro. I liked his way better than mine anyway. To get mine to work would have required some extra effort and been way more complex a formula anyway. Besides, I learned something.

    benbun earned them, as far as I'm concerned.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 05:53 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com