IF statements using multiple conditions

Lbender

New Member
Joined
Apr 18, 2019
Messages
5
E
F
G
Target
Status (Days)
Actual
3/15/2019
33
4/17/2019
4/1/2019
FALSE
3/22/2019
4/18/2019

<tbody>
</tbody>
I'm trying to calculate a field using IF statements, but I have 3 or 4 separate situations that I need calculations for.
Here is the function I'm using:
=IF(G6>=E6, TODAY()-E6), IF(G6<=E6, DATEDIF(E6,G6,"d")) but can't figure out how to work

My calc cells are above
Basically I need it to calculate the Past Due #of days if the Actual is after the target,
if the actual falls before the target then count the #of days ahead of schedule
if the actual is empty then say something like "In Progress"
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
Hi & welcome to MrExcel
How about
=IF(G6="","In Progress",IF(G6>=E6, TODAY()-E6, IF(G6<=E6, E6-G6)))
 
Last edited:

Lbender

New Member
Joined
Apr 18, 2019
Messages
5
Doesn't work, when my actual completion date is sooner, it's showing positive difference like it's past due (maybe day difference, not today function) . I would prefer this option to show - (negative). Then is it possible to populate the results in color?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
In that case how about
=IF(G6="","In Progress",IF(G6>=E6, TODAY()-E6, IF(G6<=E6, G6-E6)))
 

Lbender

New Member
Joined
Apr 18, 2019
Messages
5
That did it! Now, maybe too much, but is there a way for it to auto populate color per result? Ie- positive values= red, Neg= green, In Progress=yellow?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
Not with a normal formula, you would need to use Conditional formatting for that.
 

Lbender

New Member
Joined
Apr 18, 2019
Messages
5
Is there a tutorial on how to do that in conditional formatting? I've never messed with that before
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,739
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top