Not sure really where else to turn.
I have a spreadsheet where I need to calculate days past in one column but the rub is I need variables from a second column factored in.



Here is an example of what I mean:
I have a column A that shows a possible one of 3 criteria: Critical, Severe, and High. Each one of these has a different time value 15, 30, and 45 days respectively. These values and criteria cannot be moved from this column.

Column B has a date that the entry was created

Column C is the Days Past column that will need to be populated with the complex formula. (If it can be done)

To throw a bigger monkey wrench into the mix is the fact that the entries that have not gone past the time as a negative (indicating it has not reached the due date yet according to column A criteria)

Thanks for taking a look and Ihope someone can assist with this or even just tell me Nope can't be done without different columns for the Critical, Severe, and High parts.

Is Column A a general categorization of each record indicating how long is allotted for completion? i.e. Critical gets until Start + 15, Severe gets Start + 30? Is that right?

If so, that doesn't seem so complex. However, I am not clear on your "bigger monkey wrench". You indicate wen you want something to happen (i.e. when not past due), but not what you want to do. Do you want these to remain blank or something else?

I've worked one up but I can't seem to attach anything right now. But basically, what I did was:

Make a table (I named it "Levels") on a separate tab for the lookup values for the levels
Level Due
Critical 15
Severe 30
High 45

For the main table, I have three columns: Level, Start, Days Past. The equation for Days past in Row B is:

=IF(TODAY()-B2-VLOOKUP(A2,Level,2,FALSE)>0,TODAY()-B2-VLOOKUP(A2,Level,2,FALSE),"")

Not sure if this will help but here is what I am working with.
A B C D
RISK ISSUEDETAIL CREATEDON Days Past
HIGH Service Req 12/23/2013301
SEVERE Service Req 3/25/2013574
CRITICAL Service Req 10/13/20147

Column letters are actually different than shown above and the High, Severe, and critical in this example is not reflecting the 45, 30, and 15 days being "removed" from the calculation in Days Past column. Also since the critical entry was created on 10/13/2014 the expected completion is within 15 days of that and should reflect a -8 since there is 8 days left to have it done.
The Severe has a 30 day expectation and since it was created on 3/25/13 it reflects a positive number since it is 574 days since it was created but does not show the "free" 30 days (actual display should be 544 days).
The High should be 256 days past accounting for the "free" 45 days.

I was able to get partial formula to work using the following statement: =IF(L3="SEVERE",-30,IF(L3="CRITICAL",-15,IF(L3="HIGH",-45,)))

Now all I need is to be able to somehow incorporate =Today ()-C3 into it along with showing a negative number for those that fall withing their respective due dates.

Days Past = TODAY()-[Date Create cell]-VLOOKUP(A2,Level,2,FALSE)

Or if you don't want to create a table and do a lookup:

Days Past = TODAY()-[Date Create cell]-IF([Risk cell]="High",45,IF([Risk cell]="Severe",30,15))

Thanks gebobs the last one works great.

Only other question is there a way to make any of the ones that are within the 45, 30, and 15 days show as a -# and the rest as positive.

I really appreciate you help with this.

Nevermind gebobs, as you have it works exactly as I need. Please disregard above.

I think that's just how it should display. For the examples you provided, the calculations come to:

High 23-Dec 257
Severe 25-Mar 545
Critical 13-Oct -7

Right? This last is the only one that is "within the 45, 30, and 15 days". It displays as negative.