SOLVED

Calculating cycle time based on different criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2980614%22%20slang%3D%22en-US%22%3ECalculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980614%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20DAYS%20to%20calculate%20cycle%20time%20based%20on%20Job%20Type%2C%20which%20would%20be%20simple%20enough%2C%20but%20I'd%20like%20to%20use%20the%20IF(L2%3D%22%22%2C%22%22%2C....)%20to%20leave%20the%20cell%20blank%20if%20the%20cycle%20is%20not%20complete.%20If%20the%20Job%20Type%20is%20%22Spec%22%2C%20then%20I%20need%20the%20formula%20in%20M2%20to%20calculate%20the%20days%20calculated%20between%20L2%20and%20I2%2C%20if%20%22Build%22%2C%20then%20I%20need%20days%20between%20L2%20and%20F2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20this%20wouldn't%20normally%20be%20a%20problem%20for%20me%2C%20but%20as%20I%20said%20I'm%20trying%20to%20get%20the%20cell%20M2%20to%20be%20blank%20if%20L2%20is%20also%20blank%20(cycle%20not%20yet%20complete).%20Is%20this%20just%20not%20possible%20without%20a%20separate%20reference%20cell%20or%20something%3F%20Can%20someone%20help%3F%20I've%20been%20trying%20to%20use%20IFS%20and%20nested%20IF%20statements%2C%20but%20I%20think%20that%20perhaps%20I'm%20getting%20my%20order%20of%20logic%20messed%20up.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(sample%20file%20attached%20for%20reference)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2980614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980648%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980648%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1213069%22%20target%3D%22_blank%22%3E%40Edg38426%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(L2%3D%22%22%2C%22%22%2CL2-IF(C2%3D%22Spec%22%2CI2%2CF2))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980670%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980670%22%20slang%3D%22en-US%22%3EThis%20worked%20quite%20well%2C%20thank%20you.%20I%20guess%20I%20should%20have%20avoided%20the%20DAYS%20formula%20altogether%2C%20eh%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980688%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1213069%22%20target%3D%22_blank%22%3E%40Edg38426%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20would%20work%20as%20well%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(L2%3D%22%22%2C%22%22%2CDAYS(L2%2CIF(C2%3D%22Spec%22%2CI2%2CF2)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980692%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980692%22%20slang%3D%22en-US%22%3ESo%20what%20I'm%20seeing%20is%20that%20my%20issue%20was%20that%20I%20was%20trying%20to%20use%20two%20different%20job%20types%20in%20the%20formula%2C%20where%20I%20should%20have%20just%20been%20using%20one%20(if%20%22Spec%22%20then%20X%2C%20otherwise%20Y)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2980704%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20cycle%20time%20based%20on%20different%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2980704%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1213069%22%20target%3D%22_blank%22%3E%40Edg38426%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20assuming%20that%20the%20job%20type%20will%20always%20be%20either%20Spec%20or%20Build.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20the%20other%20hand%2C%20it%20would%20be%20possible%20to%20do%20it%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(L2%3D%22%22%2C%22%22%2CL2-IFS(C2%3D%22Spec%22%2CI2%2CC2%3D%22Build%22%2CF2))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@Edg38426 

How about

 

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

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

@Edg38426 

This would work as well:

 

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

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)

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

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.