=IF Cell = value, show value from another cell

enigmafyv

New Member
Joined
Nov 19, 2004
Messages
41
I'm trying to setup a time tracker for myself, and everything works, but essentially I'm trying to tell one cell that if my hours are over 40, then calculate how much over time I have, and conditionally format it. When I'm under 40 hours it shows the results of "#VALUE!", when I'd rather it show me the words, "No OT."

So, with this being said, I've moved that cell off the screen so I don't see the "#VALUE!" being shown, and I've setup another cell on the screen with the formula: (Notes: 1=Overtime; 0=No Overtime; $D$20=no value if no overtime is accumulated (aka #VALUE!), and the sum of overtime if it is accumulated) =IF(D10=1,Show the contents of cell $D$20,"No OT") and I write the formula like this, =IF(D10=1,=D20,"No OT").

I've put what my intentions are for my formula in green, and I've put my formula in dark blue font color.

I cannot get the value_if_true to equal another value from another cell though, using this method. So I'm guessing there's got to be another way to do this?

Any suggestions?

-Enigma[/img]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Logically, this is what you'd think, and that's what I thought as well, but I've tried that, and it doesn't show me anything in the cell, so back to square 1.

-Enigma
 
Upvote 0
Re-reading your post it seems we may have misunderstood your intent.

Are you trying to put this formula in conditional formatting or in the cell?

The formatting of your hours may be causing confusion as well

40 hours in number format is 1.66666666666667

so if your total hours are in a cell (C1) then

=IF(C1 > 1.666666666667, C1,"No OT")

C1,C2, D1, D2 formatted as [h]:mm
Book1
ABCD
1Total Hours41:00No OT
241:0041:00
Sheet1
 
Upvote 0
please re-read my post above and you should understand what I'm doing, otherwise I can explain further. Focus on cells B10 - J11 and the above is more for reference.

As far as the conditional stuff, don't worry too much about this, I know what I want to do with it, and it's not concerning what I'm asking help for.

-Enigma
 
Upvote 0
Assume your total time is in A1

In your overtime cell enter

=IF(A1>40,A1-40,"NO OT")

Note that A1 could be replaced with a formula such as SUM(A1:A7) or such, just as long as it represents the total hours worked.

lenze

Edi:: Corrected formula
 
Upvote 0
Ok, I'm dumb, why didn't I think of that? I was trying the hard way. That worked much easier that what I was trying to do.

You Rock!

Thanx
-Enigma
 
Upvote 0
Ok, now the next problem is, can I do something like this.

=IF(B11>40,=TEXT((B11-40)/24,"h:mm"),"No OT")

By doing this, the time will be in "time" format instead of "decimal" format.

I'm trying it but it's erroring out.

-Enigma
 
Upvote 0
=IF(B11>40,TEXT((B11-40)/24,"h:mm"),"No OT")

don't put the = in the second condition, it forces a true false evaluation, in which this case produces nothing to evaluation against, the result would be false.

Remove the = and it should be fine
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
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