Calculating cycle time based on different criteria

[Edg38426]

I am trying to use DAYS to calculate cycle time based on Job Type, which would be simple enough, but I'd like to use the IF(L2="","",....) to leave the cell blank if the cycle is not complete. If the Job Type is "Spec", then I need the formula in M2 to calculate the days calculated between L2 and I2, if "Build", then I need days between L2 and F2.

Again, this wouldn't normally be a problem for me, but as I said I'm trying to get the cell M2 to be blank if L2 is also blank (cycle not yet complete). Is this just not possible without a separate reference cell or something? Can someone help? I've been trying to use IFS and nested IF statements, but I think that perhaps I'm getting my order of logic messed up. 

(sample file attached for reference)

[#2 · Accepted solution]

@Edg38426 

How about

=IF(L2="","",L2-IF(C2="Spec",I2,F2))

[#3]

This worked quite well, thank you. I guess I should have avoided the DAYS formula altogether, eh?

[#4]

@Edg38426 

This would work as well:

=IF(L2="","",DAYS(L2,IF(C2="Spec",I2,F2)))

[#5]

So what I'm seeing is that my issue was that I was trying to use two different job types in the formula, where I should have just been using one (if "Spec" then X, otherwise Y)

[#6]

@Edg38426 

Yes, assuming that the job type will always be either Spec or Build.

On the other hand, it would be possible to do it like this:

=IF(L2="","",L2-IFS(C2="Spec",I2,C2="Build",F2))

[#7]

Oh, this may work better, actually. There are very rare instances where the Job Type will be "Model" (only once per worksheet). If I use the nested IFS, then I could account for this variant as well! Thank you for your help, Hans.