Calculating Difference Between Two Dates (over multiple years via months)!

[andyberger87]

Hello All,

I recently had a job interview where I was given a scenario (see attachment) and asked to explain how I would go about solving it.

While not actually having to solve it, I was able to get a copy of the solved spreadsheet and am looking for guidance on alternative ways of solving this rather than the long chain of nested IF (left, right) statements.

This is a date-related question where in columns E&P I am supposed to determine how many months of each calendar year a particular car model is scheduled to be onsale for.

The question is solved but I'm looking for help on alternative ways...any assistance is helpful

Thank you!

[JieJenn]

won't let me post

[JieJenn]

Usually I would create a helper column to extract the year and months first, but since your "puzzle" didn't specific the rule, I am guessing it is a formula only puzzle. For some reason the forum won't let me post the formula. See the attached file.

[Bo_Ry]

Please try at E3 drag across and down

=IFERROR(DATEDIF(MAX(DATE(RIGHT($C3,2)+2000,LEFT($C3,2),1),("1/1/"&E$2)),MIN(DATE(RIGHT($D3,2)+2000,LEFT($D3,2),1),("1/1/"&E$2+1)-1),"m")+1,0)