Need help creating a conditional formula

dykstjan

New Member
Joined
May 8, 2013
Messages
12
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
C
Starting Time
D
Lunch Start
E
Lunch End
F
Lunch Hour

G
Paid (P) Unpaid (U)
H
Ending Time

I
Regular Hours Worked
8:00 AM12:00 PM12:30 PM0.5 U4:00 PM
7.5


<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>



<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
Try: =IF(G1="u",(H1-C1)*24-F1,(H1-C1)*24)

This worked for me when I tested it.

Hope this helped.

David
 
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top