0

I was wondering if anyone knew how to join these two formulas together so they will work and accomplish all that is needed in one cell? Both formulas work separately but I need them to work together in the same cell. =IF(M29=FALSE,(24*(H12-D12)-F12),8) <--- this formula does not consider other possibilities, so it is insufficient, however I need it for the 8 hours if the little box is ticked(see pic below).

=IF(E12="","0.00",24*(E12-D12))+IF(G12="",(24*(H12-D12)-F12),(24*(H12-G12)))

Please notice there is a repeat in both formulas... (24*(H12-D12)-F12) which I'm assuming is redundant and not necessary to calculate twice. Below is a snippet of what I'm trying to accomplish in cell C12. If the small box is ticked, the C12(Hours Worked) value becomes 8, shown at the end of the first formula. I have to account for all possibilities... clocking-in in the morning and leaving in the morning, clocking-in in the morning and leaving in the afternoon which brings in F12 Cell for lunch break, and clocking-in in the afternoon and leaving in the afternoon.

I tried combining the two formulas in different ways on my own, but that didn't return the correct result/value.

Screenshot

Improve this question
5
  • 3
    It looks like you forgot to add the image.
    – FlexYourData
    Commented May 10 at 19:25
  • Yes, I forgot to add the image, but I think it’s added now. Thanks.
    – ubunt guy
    Commented May 12 at 16:57
  • where on your example image do you want your output to appear?
    – Excellor
    Commented May 13 at 7:15
  • @Excellor Hi, this formula is for Cell C12 which is under the column "Hours Worked". Also, I should have explained better that Cell M29 has the word FALSE typed in it so it will perform the formula if it's TRUE but if the little box is checked then 8 hours is stipulated in the Hours Worked column so the employee gets credit for a Holiday.
    – ubunt guy
    Commented May 13 at 12:34
  • This is not an answer to your full question but you mentioned some piece of the formula appears twice. If this becomes cumbersome, you may be able to use the LET() function, depending on your version of Excel. It lets you store variables and reference later by name instead of duplicating the formula.
    – Engineer Toast
    Commented May 13 at 18:41

1 Answer 1

Reset to default
0

I took a slightly different approach, but I think it gives you the desired output:

=IF(M29=TRUE,8,IF(G12<>"",24*(H12-G12)+24*(E12-D12),IF(D35<>"",24*(E12-D12),"")))

So what does it do: First check if M29 is TRUE or FALSE; when true (the checkbox is checked), you just output a plain 8;

Then checks if cell G12 is something else than empty, if so, it calculates for the entire day.

Then, if it still has no calculation, it'll check for the morning check-in and calculate accordingly.

It however, doesn't take the break into calculation, because I'm assuming that the break equals the difference between check-out in the morning and check-in in the afternoon. And everybody takes their break.

Here are a few examples of what's possible: (first column is where the formula is applied) enter image description here

EDIT 2:

=IF(M29=TRUE,8,(IF(AND(D12="",G12=""),0,IF(D12="",24*(H12-G12),IF(G12="",24*(E12-D12),24*(H12-D12-F12)))))) This one should do the trick, please give it a thorough test and let me know if it fits.

Improve this answer
8
  • Thank you, almost perfect... how would we NOT have a 30 minute break in your example for folks who come-in in the morning and leave in the morning and for folks who come-in the afternoon and leave in the afternoon?... these folks aren't allowed a lunch break.
    – ubunt guy
    Commented May 13 at 15:12
  • @ubuntguy Everybody deserves their break, I'm just assuming they clocked out and back in, but if they don't I'll adjust the formula again.
    – Excellor
    Commented May 13 at 15:29
  • Yes please adjust the formula. I guess the policy is you don't get a break if you work less than 8 hours a day. However, if you work 8 hours or more you are required to have a break. Also, is D35 in your formula a typo?
    – ubunt guy
    Commented May 13 at 17:08
  • @ubuntguy D35 was indeed a typo, I have edited the entire formula, should work now.
    – Excellor
    Commented May 13 at 18:11
  • I'm getting a -14 for Hours Worked in the C column. Do you know why? Also, when I click the little box, nothing happens... I don't get an 8 in the Hours Worked column. This is entering the following times: 7:00 AM 12:00 PM 1.00(Break) 1:00 PM 5:00 PM
    – ubunt guy
    Commented May 13 at 18:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .