Need help creating a conditional formula

[dykstjan]

I am trying to create a formula that will calculate a formula one way if a cell has a value of "P" and a different way if the cell has a value of "U". The formula is in a time sheet. If lunch hour is 'P", then it should not subtract the value. If it is "U" it should subtract the value. The formula in Column G is (H1-C1)*24 -F1. Thank you for your help
[TABLE="width: 706"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 706"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 706"]
<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]C
Starting Time[/TD]
[TD]D
Lunch Start[/TD]
[TD]E
Lunch End[/TD]
[TD]F
Lunch Hour[/TD]
[TD]
G
Paid (P) Unpaid (U)[/TD]
[TD]H
Ending Time[/TD]
[TD]
I
Regular Hours Worked[/TD]
[/TR]
[TR]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]12:30 PM[/TD]
[TD="align: right"]0.5[/TD]
[TD] U[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD]
7.5

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

in G
=(H1-C1)*24-(F1="U")*F1

 

[dykstjan]

Thanks
It's returning the wrong answer though. It's giving a total of "6" in column I. When it's U, the total should be 5.5. It's returning "6" if it's U or P. It should return 5.5 if "u" and 6 if "P". I want it to subtract if "U" and not subtract if "P"

in G
=(H1-C1)*24-(F1="U")*F1

 

[dcofer]

Try: =IF(G1="u",(H1-C1)*24-F1,(H1-C1)*24)

This worked for me when I tested it.

Hope this helped.

David

 

[dykstjan]

It is returning a value of "6" whether I have U or P..

Try: =IF(G1="u",(H1-C1)*24-F1,(H1-C1)*24)

This worked for me when I tested it.

Hope this helped.

David

 

[dcofer]

It is returning a value of "6" whether I have U or P..

It returned 7.5 for me when G1 was "u" and 8.0 when G1 was P.

Make sure both C1 and H1 are formatted properly and the time shows 8:00 AM in C1 and 4:00 PM in H1. The first time I entered the data in my spreadsheet, H1 was showing 4:00 AM for some reason, but when I changed it to PM, it worked properly.

Also make sure F1 is ".5" When I first tested the formula, Excel wanted to make F1 a time format instead of a number format. You might check that too.

 

[dykstjan]

Thank you for your help! It's still not returning the different values for "U" or "P". I copied the formula in as per your example (it's actually row 11.) i put the formula in column G =IF(G11="U",(H11-C11)*24-F1,(H11-C11)*24)

The value "U" is a text value. Do you see anything I might be missing?

 

[dykstjan]

I just re-read my original post.
I don't have a formula in column G. That is where I'm typing "U" or "P" I am placing the formula in column I. Am I doing it wrong? Sorry if I have confused the question. Also I do have value formula in column F so it's not a date
Thank you!
=======

It returned 7.5 for me when G1 was "u" and 8.0 when G1 was P.

Make sure both C1 and H1 are formatted properly and the time shows 8:00 AM in C1 and 4:00 PM in H1. The first time I entered the data in my spreadsheet, H1 was showing 4:00 AM for some reason, but when I changed it to PM, it worked properly.

Also make sure F1 is ".5" When I first tested the formula, Excel wanted to make F1 a time format instead of a number format. You might check that too.

 

[dcofer]

Thank you for your help! It's still not returning the different values for "U" or "P". I copied the formula in as per your example (it's actually row 11.) i put the formula in column G =IF(G11="U",(H11-C11)*24-F1,(H11-C11)*24)

The value "U" is a text value. Do you see anything I might be missing?

The formula you quoted above shows F1, not F11. If this in in row 11, then "F1" should be "F11"

I don't see anything else that you are missing.

 

[dykstjan]

That was it. I'm such an idiot! Thank you again

The formula you quoted above shows F1, not F11. If this in in row 11, then "F1" should be "F11"

I don't see anything else that you are missing.