Results 1 to 6 of 6

Thread: IF Formulas...?

  1. #1

    Post IF Formulas...?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey guys, I've been having a hard time figuring out a specific formula for the following:

    I need a formula that splits hours of work between 00:00 and 30:00 (6:00). I call this magic hour.

    e.g. I start work at 10pm and finish at 4am. I had a one hour break unpaid break. Therefore I worked 5 hours. 3 of these were magic hours.

    J8
    START TIME
    K8
    END TIME
    L8
    BREAKS
    M8
    REGULAR HOURS
    N8
    MAGIC HOURS
    22:00 04:00 1 2 3

    e.g. I start at 3am and finish at 7am. Therefore I worked 4 hours. 3 of these is magic hour.

    J8
    START TIME
    K8
    END TIME
    L8
    BREAKS
    M8
    REGULAR HOURS
    N8
    MAGIC HOURS
    03:00 07:00 1 3

    How do I get this to auto-fill? What formula would I use?

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by excelproblems View Post
    Hey guys, I've been having a hard time figuring out a specific formula for the following:

    I need a formula that splits hours of work between 00:00 and 30:00 (6:00). I call this magic hour.

    e.g. I start work at 10pm and finish at 4am. I had a one hour break unpaid break. Therefore I worked 5 hours. 3 of these were magic hours.

    J8
    START TIME
    K8
    END TIME
    L8
    BREAKS
    M8
    REGULAR HOURS
    N8
    MAGIC HOURS
    22:00 04:00 1 2 3

    e.g. I start at 3am and finish at 7am. Therefore I worked 4 hours. 3 of these is magic hour.

    J8
    START TIME
    K8
    END TIME
    L8
    BREAKS
    M8
    REGULAR HOURS
    N8
    MAGIC HOURS
    03:00 07:00 1 3

    How do I get this to auto-fill? What formula would I use?
    Im a bit uncertain about the sentence Ive underlined, but I think your saying that hours worked between midnight and 6am are "Magic" hours?
    If I work some regular hours and some magic ones consecutively, as in your first example, how do we determine which one has the break period in it?

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    As a start, for calculating the magic hours, try this in Cell N8:

    =IF(AND(J8 < 6/24,K8<= 23.99/24), 6/24-K8-L8, IF(AND(J8>6/24,K8<=6/24),K8-L8,IF(K8<J8,6/24-L8,0)))

    This seems to work on your examples. In your time cells, you need to enter times in the format HH:MM
    If this is ok, a separate formula based on the same principle will calculate the regular hours. Im not sure about the breaks, so Ive assumed that they will be entered in L8, and be deducted from any magic hours.
    Last edited by Hercules1946; 2014-11-06 at 10:42 PM.

  4. #4

    Problem?

    I used your formula with my actual cell numbers and it has not worked.

    =IF(AND(G7 < 6/24,H7<= 23.99/24), 6/24-H7-I7, IF(AND(G7>6/24,H7<=6/24),H7-I7,IF(H7<G7,6/24-I7,0)))

    I put in hours 10:00 to 25:00 (01:00) which should have marked 1 hour in cell "N8" but instead nothing happened.

    ________________________________________________________________________________________________________

    Quote Originally Posted by Hercules1946 View Post
    As a start, for calculating the magic hours, try this in Cell N8:

    =IF(AND(J8 < 6/24,K8<= 23.99/24), 6/24-K8-L8, IF(AND(J8>6/24,K8<=6/24),K8-L8,IF(K8<J8,6/24-L8,0)))

    This seems to work on your examples. In your time cells, you need to enter times in the format HH:MM
    If this is ok, a separate formula based on the same principle will calculate the regular hours. Im not sure about the breaks, so Ive assumed that they will be entered in L8, and be deducted from any magic hours.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Hello and Im sorry for the delay in responding but I didn't get the usual email notification of your response. I hadn't anticipated that you would be working with times above 24 hours. If you enter the time as 01:00, this gives the correct answer, but if you cant do that then we will need more IF statements.

    1. Will this apply to the Start times as well as the End times?
    2. Can we add an extra helper cell to recalculate the time if its above 24? If so this will simplfy the formula modifications.
    3. Do you know the maximum time value to allow for?

    Try these modifications with your example in #4:

    In I8: =IF(K8>1,MOD(K8,1),K8)
    In N8: =IF(AND(J8 < 6/24,I8<= 23.99/24), 6/24-I8-L8, IF(AND(J8>6/24,I8<=6/24),I8-L8,IF(I8<J8,6/24-L8,0)))

    This uses I8 as a helper cell and will work provided that your time differences don't exceed 24 hours.

  6. #6
    This still hasn't worked.

    This is the document, maybe it will make more sense with all the cells?


    onedrive.live .com/redir?resid= 61353C293FE6FEF5%211362 < with no spaces

Tags for this Thread

Posting Permissions

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