Seeking help to solve problem regarding checking & counting a date within a period

[Ben9108]

I would like to seek someone's assistance to solve the following problems.

According to the current maintenance contract period (blue), two test dates are to be conducted in 3 & 5 years respectively within the contract period.

In orange columns, it is designed to check whether tests are completed in 3 & 5 years respectively.

In green columns, two dates of tests are to be indicated in the cells according to the current contract period.

First Test to be conducted after the commencement of the contract within 3 years
The second (5 years) test is to be conducted 6 months in advance based on the last date of the contract.

[josephteh]

Try this: Q7=IF(YEAR(EOMONTH($D7,36-1))=Q$5,EOMONTH($D7,36-1),IF(YEAR(EOMONTH($E7,-6)+1)=Q$5,EOMONTH($E7,-6)+1,"")), copy across and down.

[Ben9108]

Thanks, josephteh, I have obtained the result successfully. Would it be possible to help to solve the following problems?


According to the current maintenance contract period (blue), two test dates are to be conducted in 3 & 5 years respectively within the contract period.

In orange columns, it is designed to check whether tests are completed in 3 & 5 years respectively.

Please help to make comments on the following formulas to reach the expected results shown in the attached file. Thanks

O7=IF(COUNTA($F7:$L7),ROUND(3-YEARFRAC(MAX($F7:$L7),TODAY(),1),1),"No Record"
P7=IF(COUNTA($F7:$L7),ROUND(2-YEARFRAC(MAX($F7:$L7),TODAY(),1),1),"No Record")

[josephteh]

Try this,

O7=IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7<EDATE(D7,36)))>0,"Tested",IF(AND(P7<>"Tested",EOMONTH($D7,36-1)>TODAY()),(EOMONTH($D7,36-1)-TODAY())/365,"No Record")), copy down.

P7=IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7>=EDATE(D7,36))*(F7:N7<EDATE(D7,60)))>0,"Tested",(EOMONTH($E7,-6)+1-TODAY())/365), copy down.

[Ben9108]

Thanks, josephteh, I found the formula can fix the problem, but there is a minor problem.

The cells indicated "#VALUE!" after my modification of the formulas shown below.
Would you mind letting me know the problem with the amended formulas? Thanks

O7=ROUND(IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7<EDATE(D7,36)))>0,"Tested",IF(AND(P7<>"Tested",EOMONTH($D7,36-1)>TODAY()),(EOMONTH($D7,36-1)-TODAY())/365,"No Record")),1)
P7=ROUND(IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7>=EDATE(D7,36))*(F7:N7<EDATE(D7,60)))>0,"Tested",(EOMONTH($E7,-6)+1-TODAY())/365),1)

[josephteh]

Try this:

O7=IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7<EDATE(D7,36)))>0,"Tested",IF(AND(P7<>"Tested",EOMONTH($D7,36-1)>TODAY()),ROUND((EOMONTH($D7,36-1)-TODAY())/365,1),"No Record"))

P7=IF(SUMPRODUCT((F7:N7)*(F7:N7>=D7)*(F7:N7>=EDATE(D7,36))*(F7:N7<EDATE(D7,60)))>0,"Tested",ROUND((EOMONTH($E7,-6)+1-TODAY())/365,1))