SOLVED

# Calculating cycle time based on different criteria

Occasional Contributor

# Calculating cycle time based on different criteria

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)

6 Replies
best response confirmed by Edg38426 (Occasional Contributor)
Solution

# Re: Calculating cycle time based on different criteria

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

# Re: Calculating cycle time based on different criteria

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

# Re: Calculating cycle time based on different criteria

This would work as well:

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

# Re: Calculating cycle time based on different criteria

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)

# Re: Calculating cycle time based on different criteria

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))

# Re: Calculating cycle time based on different criteria

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.