# Thread: IF Formulas...?

1. ## IF Formulas...?

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?  Reply With Quote

2. Originally Posted by excelproblems 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?  Reply With Quote

3. 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.  Reply With Quote

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.

________________________________________________________________________________________________________ Originally Posted by Hercules1946 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.  Reply With Quote

5. 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.  Reply With Quote

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  Reply With Quote

#### Tags for this Thread

formula, hours, time function #### Posting Permissions

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