Have a workbook with muliple linking of sheets.
Sheet 1 is roster with names listed in C11:C60.
Problem is with sheets 2-10 that is various certifications of staff.

On Sheets 2-10,
Column C: Last Name; C11:C60 is linked to Roster (sheet 1) C11:C60 Locked.
No issues.
Column D: First Name; D11-D60 is linked to Roster (sheet 1) D11:D60 Locked.
No issues.

Column E: Initial Certification Date; Unlocked, for direct entry. No Issues.

Column F: Certification Term (Months) = "24" ONLY if a name is listed in
Column C (linked to Roster sheet). This is for cosmetic reasons only so if a
name is removed from Roster sheet, the corresponding cell in Column F is
blanked.

Expressed as formula: "=IF(C11=0,"","24"),

Locked. No apparent issues. This column will probably be hidden in final
version.

Column G: Renewal Date= E+F ONLY if a date is listed in Column E. If Column
E is blank, G is also blank (as is Column F by above section.

Expressed as formula: "=IF(E11=0,"",DATE(YEAR(E11),MONTH(E11)+(F11),0))" .

Locked. No apparent issues.

Column H: Todays Date: Inserted to calculate time interval between today's
date and due date for recertification (Column G). For cosmetic reasons, cell
is blanked if no name is present in Column C.

Express as formula: "=IF(C11=0,"",TODAY())

Locked. No apparent issues. Will probably be hidden in final version.

All above formulas "seem" to work properly.

Column I: "Time Until Renewal (Months, Days)" PROBLEM AREA.

What I want this Column I to do is:
If Column C is blank, corresponding Column I cell is also blank. This is for
cosmetic reasons so if a name is removed from roster, cell in Column I will
be blanked.
If Column C Is NOT blank but Column E is blank, "LATE" will appear. This is
to indicate that if person listed in roster can't provide a certification
date, it is considered to be outdated.
If Column C is NOT blank AND Column G occurs in the past, "LATE" appears.
For obvious reasons, the certification is outdated.
If Column C is NOT blank AND Column G occurs in the future, the calculation
is performed to calculate the time in months/days until renewal is due.

Formula for Column I thus far:
"=IF(AND(C260,G260,G26H26),"LATE",DATEDIF(H26,G 26,"M"))

The problem is:

When C is empty, I get a "#Value!" in Column I corresponding cell when it
should be blank.
Dates that occur in the future still get a "LATE" value in Column I if
Column C-H contain values. It should calculate the Months/Days until renewal
is due.
I also need help changing the "M" so it will calculate months and days until
expiration of certification. I have tried "md" but it doesn't calculate what
I want.

Thanks for your help,

L.M

UPDATED FORMULA:
=IF(AND(C260,G260,H260,G26<H26),"LATE",YEAR(G26 )-YEAR(H26)-IF(OR(MONTH(G26)<MONTH(H26),AND(MONTH(G26)=MONTH(H 26),DAY(G26)<DAY(H26))),1,0)&"years,"&MONTH(G26)-MONTH(H26)+IF(AND(MONTH(G26)<=MONTH(H26),DAY(G26)< DAY(H26)),11,IF(AND(MONTH(G26)<MONTH(H26),DAY(G26) =DAY(H26)),12,IF(AND(MONTH(G26)MONTH(H26),DAY(G2 6)<DAY(H26)),-1)))&"months,"&G26-DATE(YEAR(G26),MONTH(G26)-IF(DAY(G26)<DAY(H26),1,0),DAY(H26))&" days")

Formula now correctly performs funciton of calculating renewal date if in
the future and displaying "LATE" if in the past ONLY if data is entered in
columns A-H. I still get #value! in Column I cells if Columns C-H are blank
and can't seem to force it to leave Column I cells blank if corresponding
data in Columsn A-H are blank.

Anyone with any solution?

"EDSTAFF" wrote:

Have a workbook with muliple linking of sheets.
Sheet 1 is roster with names listed in C11:C60.
Problem is with sheets 2-10 that is various certifications of staff.

On Sheets 2-10,
Column C: Last Name; C11:C60 is linked to Roster (sheet 1) C11:C60 Locked.
No issues.
Column D: First Name; D11-D60 is linked to Roster (sheet 1) D11:D60 Locked.
No issues.

Column E: Initial Certification Date; Unlocked, for direct entry. No Issues.

Column F: Certification Term (Months) = "24" ONLY if a name is listed in
Column C (linked to Roster sheet). This is for cosmetic reasons only so if a
name is removed from Roster sheet, the corresponding cell in Column F is
blanked.

Expressed as formula: "=IF(C11=0,"","24"),

Locked. No apparent issues. This column will probably be hidden in final
version.

Column G: Renewal Date= E+F ONLY if a date is listed in Column E. If Column
E is blank, G is also blank (as is Column F by above section.

Expressed as formula: "=IF(E11=0,"",DATE(YEAR(E11),MONTH(E11)+(F11),0))" .

Locked. No apparent issues.

Column H: Todays Date: Inserted to calculate time interval between today's
date and due date for recertification (Column G). For cosmetic reasons, cell
is blanked if no name is present in Column C.

Express as formula: "=IF(C11=0,"",TODAY())

Locked. No apparent issues. Will probably be hidden in final version.

All above formulas "seem" to work properly.

Column I: "Time Until Renewal (Months, Days)" PROBLEM AREA.

What I want this Column I to do is:
If Column C is blank, corresponding Column I cell is also blank. This is for
cosmetic reasons so if a name is removed from roster, cell in Column I will
be blanked.
If Column C Is NOT blank but Column E is blank, "LATE" will appear. This is
to indicate that if person listed in roster can't provide a certification
date, it is considered to be outdated.
If Column C is NOT blank AND Column G occurs in the past, "LATE" appears.
For obvious reasons, the certification is outdated.
If Column C is NOT blank AND Column G occurs in the future, the calculation
is performed to calculate the time in months/days until renewal is due.

Formula for Column I thus far:
"=IF(AND(C260,G260,G26H26),"LATE",DATEDIF(H26,G 26,"M"))

The problem is:

When C is empty, I get a "#Value!" in Column I corresponding cell when it
should be blank.
Dates that occur in the future still get a "LATE" value in Column I if
Column C-H contain values. It should calculate the Months/Days until renewal
is due.
I also need help changing the "M" so it will calculate months and days until
expiration of certification. I have tried "md" but it doesn't calculate what
I want.

Thanks for your help,

L.M