=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]
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

enigmafyv

New Member
Joined
Nov 19, 2004
Messages
41
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
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446

ADVERTISEMENT

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
 

enigmafyv

New Member
Joined
Nov 19, 2004
Messages
41
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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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
 

enigmafyv

New Member
Joined
Nov 19, 2004
Messages
41
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
 

enigmafyv

New Member
Joined
Nov 19, 2004
Messages
41
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
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,391
Members
414,448
Latest member
Jessica 22664

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
Top